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 *