Tag: network path

MSSQL SERVER: Backup and Restore to Network Path Using XP_CMDSHELL

There might be times that you need to backup or restore databases in MSSQL Server to a network location. Out of the box, this isn’t the easiest thing to do. So I will show you the steps you can take to successfully backup and restore databases to and from network mapped drives.

Start by mounting a network drive (optional):

Enter the path for the network drive and click Next:

You will be prompted to enter credentials, do so then Finish

You’ll now see a new drive under Network Locations with your given drive letter:

However, if you go to SSMS and try to backup a database via the GUI you won’t be able to see the drive letter:

If you try to do it via script instead, you’ll get an operating system error:

Msg 3201, Level 16, State 1, Line 1

Cannot open backup device ‘Z:\test.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.

This is because SQL Server is registered and running as a service running under the context of the SQL Server service account. The service runs in the environment of the local console with the security of this service account. Network mapped drives are specific to a session and not visible to services started on the local console.

So how do we work around this? By using XP_CMDSHELL.

First, make sure XP_CMDSHELL is enabled:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO

Next, use the following command, replacing Z: \\mymachine\localvmshare with your own network path:

EXEC XP_CMDSHELL 'net use Z: \\mymachine\localvmshare'

The drive should now be mapped. You can test that it is properly mapped with the following:

EXEC XP_CMDSHELL 'Dir Z: '

If you mistype the path you might see an error like these:

System error 53 has occurred. The network path was not found.

If you get this error just correct the typo and run the command again.

NOTE: To remove the mapping once you are done with it, or to remove a mapping that was mistyped, you can run the following command: EXEC XP_CMDSHELL 'net use Z: /delete'

Another error you could possibly get is this one:

Enter the user name for ‘mymachine’:

The operation was canceled by the user.

If this occurs, modify the XP_CMDSHELL statement like so, where serviceaccount is a user that has permission to mount the network drive and passwordgoeshere is the password for the user account:

EXEC XP_CMDSHELL 'net use Z: \\mymachine\localvmshare /user:serviceaccount passwordgoeshere'

Now, in the GUI, we should be able to see our newly mapped drive in the backup/restore wizard:

If we rerun our script to backup, it is now successful:

And there is our backup file:

NOTE: The next time the SQL Server Service is restarted, the mapping will be removed and if you have automated backup jobs configured to use these drives, they will fail. If you decide to rely on this method for network drive backups, you will need to add logic to your backup jobs to check that the drive mapping exists first or you will need to have a startup proc configured to run and map the drive on restart.

In the case of the client I was working with, we needed to setup database mirroring via backup and restore but the local machine did not have enough disk space free to accommodate the backups. However, it is work noting that XP_CMDSHELL is a powerful feature that runs under the context of the SQL Server service account. Malicious users sometimes attempt to elevate their privileges by using XP_CMDSHELL so you should keep this in mind when deciding if you should use XP_CMDSHELL in your maintenance/backup strategy on a permanent basis.

For more information on XP_CMDSHELL, see here.