When i am taking the backup in network share drive with mention below TSQL.
Use DatabaseName Go Backup Database DatabaseName To Disk=N'H:DatabaseBackupDatabaseName.bak'; Go
I am getting the following below error in SSMS.
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device
'H:DatabaseBackupDatabaseName.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.
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. ‘fileserverfilesharebackupsbackupfile.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..
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.
drive name : Letter used to map the drive share name : UNC path to the share