Backup Error with Msg 3201, Level 16, State 1, Line 1 & Operating system error 3

Question

When i am taking the backup in network share drive with mention below TSQL.

Use DatabaseName
Go
Backup Database DatabaseName
To Disk=N'H:\DatabaseBackup\DatabaseName.bak';
Go

I am getting the following below error in SSMS.

 Msg 3201, Level 16, State 1, Line 1

Cannot open backup device 'H:\DatabaseBackup\DatabaseName.bak'. Operating system error 3(The system cannot find the path specified.).

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

As I have checked to 'xp_cmdshell' through this TSQL . The run_value & config_value as follows:

name       minimum  maximum config_value    run_value
xp_cmdshell 0           1     1               1

Note : Here H is my Network share.

Any suggestion will be appreciated.

Answer 1

SQL Server can only see network drives that are mapped in the Windows Profile of the SQL Server service account. So you'd have to log on as that account (not your own) and map the drive, for SQL Server to be able to use it for backups. Backups run using the credentials of the SQL Server service account, not yours. As suggested in the comment above, try using a full UNC patch instead (e.g. '\fileserver\fileshare\backups\backupfile.bak' which will not require mapped drives.

Note that the SQL Server service account will require read/write permissions to the destination. Being able to access external file system is one very good reason to use a domain account for your SQL Server service account, rather than LOCAL SYSTEM etc..

Answer 2

I have gone through MSDN BOL blog How to backup SQL Server databases to a mapped drive and run the below query.

Run the following command from a query window  EXEC xp_cmdshell ‘net use <drivename> <share name>’

After that i am able to take backup in Map Drive.

where

drive name : Letter used to map the drive


share name : UNC path to the share

I have also followed Here and Here during my reference.