Backup of Database ,getting error on Network

Question

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

Answer 1

Hi @Analyst_SQL ,

> 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.

Answer 2

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.

Answer 3

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.

Answer 4

Hi @Analyst_SQL ,

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