How to backup SQL Server databases to a mapped drive using Username and Password

First of all we need to enable xp_cmdshell option on our SQL server. This option enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system. By default, the xp_cmdshell option is disabled on new installations and can be enabled by using the Policy-Based Management or by running the sp_configure system stored procedure as shown in the following code example:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
-- To update the currently configured value for advanced options.
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
-- To update the currently configured value for this feature.

After that you can add as a first step on your backup job command like this one:

EXEC xp_cmdshell 'net use /USER:username x: \\ComputerName\ShareName password'

Where you need to replace username and password with yours and \\ComputerName\ShareName with your server’s name and the name of shared folder you wan to use.

Second step can be a Maintenance Plan for backup and as a third step is good to have command like this:

EXEC xp_cmdshell 'net use x: /delete'

To remove mounted folder.


Leave a Reply

Your email address will not be published. Required fields are marked *