question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked ErlandSommarskog commented

Backup of Database ,getting error on Network

When i am trying to take backup on network devices of sql,then below error is coming

Msg 3201, Level 16, State 1, Line 3
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Backup\192.168.1.117\d$\BackupFull_MedicalStore1_Backup_2021_06_08_T_12_25_22.bak'. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.

Below is my procedure

 alter procedure sp_generate_full_backup
 as
 begin
 DECLARE @Date VARCHAR(30)
 DECLARE @FileName VARCHAr(max)
 DECLARE @DBName VARCHAR(150)
 DECLARE @BkpPath VARCHAR(max)
 DECLARE @backupCommmand nvarchar(max)
 declare @DBcount int
 declare @i int = 0
 create table #UserDatabases(Name varchar(500))
 insert into #UserDatabases select name from sys.databases where database_id>4
 set @DBcount=(select count(1) from #UserDatabases)
 While (@DBcount>@i)
 Begin
 set @DBName = (select top 1 name from #UserDatabases)
 set @Date = replace(Convert(VARCHAR(10),Getdate(),23),'-','_') + '_T_' + replace(Convert(VARCHAR(10),Getdate(),108),':','_')
 set @FileName = 'Full_' + @DBName + '_' + 'Backup' + '_' +@Date +'.bak'
 set @BkpPath = '\\192.168.1.117\d$\Backup'
 set @FileName = @BkpPath + @FileName
 set @backupCommmand='Backup database [' +@DBName +'] to Disk= ''' +@FileName +''' WITH  NOFORMAT, NOINIT ,SKIP, NOREWIND, NOUNLOAD, STATS = 10'
 --Print @backupCommmand
 EXEC sys.sp_executesql @backupCommmand
 delete from #UserDatabases where name=@DBName
 Set @i=@i+1
 end
 end
sql-server-generalsql-server-transact-sql
· 12
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Just a note: All shares with a dollar sign at the end like here D$ are dedicated admin share and only (local) admin can access that share, so better create a common share and grant the required access permissions

0 Votes 0 ·

i have given all permission and giving below path

\\192.168.1.117\Backup

but issue still same

0 Votes 0 ·

Hi @akhterhussain-3167,

Why the file path in the error message does not match the code you posted?

0 Votes 0 ·
Show more comments
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

Hi @akhterhussain-3167,

Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Backup\192.168.1.117\d$\BackupFull_MedicalStore1_Backup_2021_06_08_T_12_25_22.bak'. Operating system error 3(The system cannot find the path specified.).

Please make sure the backup file location is correct. The file Location is 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Backup\192.168.1.117\d$\’ or '\\192.168.1.117\d$\Backup'? Local drive or network folder? The file location is different between your error message and store procedure.

Possible Causes & Solution of Operating System Error 3;

Cause 1: Lack of Permissions

Solution: To solve the issue first, check the permissions that a particular is having. To do the same, right-click the folder and go to the Properties >> Security tab. After that, confirm the SQL Server service account has both permissions i.e. read & write for that particular folder.

Cause 2: Unable to Locate Mapped Drive

Solution: While setting up “temporary directory for storing backup files” to a network drive, it is always suggested to use fully qualified UNC path (e.g. \\remote_server\share_DB) rather than the mapped drive (e.g. Z:\ where Z is the mapped drive letter) for the temporary directory.

Cause 3: Trust Issues between the domains

The lack of trust between the domains can also be one of the cause if the SQL Server installation and the database backup folder resides on separate computers or active directory domains. It can occur even if the SQL Server account has the full permissions.

Solution:

In order to resolve this issue, make sure that domain-to-domain trust is maintained properly, and also set up SQL Server service account using pass-through authentication between the two domains.

Please refer to the blog SQL SERVER OPERATING SYSTEM ERROR 3: GET A SOLUTION HERE to get more information.


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.





5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

set @backupCommmand='Backup database [' +@DBName +'] to Disk= ''' +@FileName +''' WITH NOFORMAT, NOINIT ,SKIP, NOREWIND, NOUNLOAD, STATS = 10'
--Print @backupCommmand
EXEC sys.sp_executesql @backupCommmand

Note that this can be simplified:

BACKUP DATABASE @DBName TO DISK = @FIleName WITH  NOFORMAT, NOINIT ,SKIP, NOREWIND, NOUNLOAD, STATS = 10

BACKUP accepts variables for its arguments, so no need for dynamic SQL.

As for the file path problem, add a PRINT of the @FileName variable. The error message does not match the code you posted.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

In the end, it may be better to backup to a local drive, and then have a separate job step to copy the file.

If we overlook the fact that you are not able to share a script that matches the error message you post, backing up a network drive comes with some challenges.

The service account for SQL Server needs to have access to the network drive. If the service account is a domain account, that is not a problem. The same is true, I believe, if SQL Server runs under a gMSA, a group Machine Service Account.

But if the service account is something like NT Service\MSSQLSERVER, that is a local service SID, you cannot grant permissions to that account on a difference machine, because the account is local to the the SQL Server machine. You can grant access to DOMAIN\MACHINE$, that is the machine account for the SQL Server machine, but this is somewhat dubious from a security perspective.

And if you don't have a domain at all, but only have a workgroup - just forget about backing up directly to a network drive. That's an uphill battle.

A job step to copy a file could be a CmdExec job that runs under a proxy account, which can be a normal Windows account, which avoids the issues I have discussed here.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered ErlandSommarskog commented

Hi @akhterhussain-3167,

Are you trying to backup all user databases? Try below T-SQL and share us the result. If i misunderstood, please let me know.

 DECLARE @databaseName VARCHAR(20); 
 DECLARE @PATH VARCHAR(MAX); 
 DECLARE @DBcount INT = 0; 
 DECLARE @DBNames TABLE (ID INT IDENTITY(1,1) primary key, name nvarchar(20));
 DECLARE @LoopCount INT = 1; 
    
 SELECT @DBcount = (select COUNT(name) from sys. databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')) 
    
 INSERT INTO @DBNames 
    
 SELECT name from sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') 
    
 WHILE @LoopCount <= @DBcount 
 BEGIN  
    
     SET @databaseName = (SELECT name FROM @DBNames WHERE ID = @LoopCount) 
     SELECT @PATH = N' \\192.168.1.117\Backup\' + @databaseName + '.bak' 
    
     BACKUP DATABASE @databaseName TO  DISK = @PATH  WITH NOINIT,NOUNLOAD, NOSKIP, STATS = 10,NOFORMAT 
     SET @LoopCount = @LoopCount + 1 
        
 END; 
    
 GO 


· 9
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

here is error below.

107615-bakup.png


0 Votes 0 ·
bakup.png (195.5 KiB)

What about removing the leading space before the double backslashes in the @PATH variable?

0 Votes 0 ·

still getting error,after removing space ,before the @path

      SELECT @PATH = N'\\192.168.1.117\Backup\' + @databaseName + '.bak' 

Msg 3201, Level 16, State 1, Line 19
Cannot open backup device '\\192.168.1.117\Backup\atlantic1506.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 19
BACKUP DATABASE is terminating abnormally.




0 Votes 0 ·
Show more comments