Link to home
Start Free TrialLog in
Avatar of CityInfoSys
CityInfoSysFlag for United States of America

asked on

Running a backup script on Sql 2014 to a UNC path fails with error

Running a backup script on Sql 2014 to a UNC path fails with error (operating system error code 3(the system cannot find the path specified). We have tried to give full rights to the folder but nothing seems to be working. The backup job does works fine if we send it to the C:\ drive. Is there anything else we can try?
Thanks

Script i'm running:
--- Automatically do Full Backup of all databases simple Recovery Mode

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name 

SET @path = '\\server-4\c$\BackupLocation\'  --Case Sensitive

SELECT @fileDate = CONVERT(VARCHAR(20),GETDAT<wbr ></wbr>E(),112) 
   + '_' 
   + REPLACE(CONVERT(VARCHAR(20<wbr ></wbr>),GETDATE(<wbr ></wbr>),108),':'<wbr ></wbr>,'')

DECLARE db_cursor CURSOR FOR  
SELECT name
FROM master.dbo.sysdatabases 
WHERE
    name NOT IN (N'tempdb') AND
    DATABASEPROPERTYEX(name, N'Status') IN ( N'EMERGENCY', N'ONLINE' )

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name 

WHILE @@FETCH_STATUS <> -1   
BEGIN   
       SET @fileName = @path + @name + '_Full_' + @fileDate + '.bak'  
       PRINT 'Backup Database command being issued on database "' + @name + '".' 
       BACKUP database @name TO DISK = @fileName WITH CONTINUE_AFTER_ERROR 
       --IF @@ERROR > 0
       --BEGIN 
           --add code here to record and/or notify yourself that an error occurred during BACKUP
       --END --IF
       FETCH NEXT FROM db_cursor INTO @name   
END  

CLOSE db_cursor   
DEALLOCATE db_cursor 

--REF
--Simple: No log backups. Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space. 
--Full: Requires log backups. No work is lost due to a lost or damaged data file. Can recover to an arbitrary point in time (for example, prior to application or user error). 

Open in new window

Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Who's the user that is running the script? It need to have access in the share.
Avatar of CityInfoSys

ASKER

It is the sa account.
SA account it's a SQL login and not a Windows login. You can't give permissions on a share to SA, right?
The SQL Server account or SQL Agent account are domain accounts?
Under services they are both the default log on as (NT AUTHORITY\System). But no they are not domain accounts. Could I just change the log on as to a local user?
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If i change the NT AUTHORITY\System on the SQL agent service will it cause any issues (besides affecting backups) while the server is running?
I was wrong the sql server agent log on as is NT Service\SQLSERVERAGENT.