Enable SQL Server to backup on remote machines/drives

Solution 1:

Use the UNC path when specifying the destination-- the SQL Agent doesn't have a concept of "mapped" "drives".

Also, SQL Agent typically runs as "Local Service" or "Local System" and, as such, doesn't have rights to remote shares on other computers.

You have a couple of choices:

• Run SQL Agent as a role account in the domain. Grant that account permission to write to the directory / share where you'd like the backups stored.

• Run SQL Agent as "Network Service". It will authenticate to the sharing server with the domain computer account of the machine the service is running on. Grant that account permission to write to the directory / share where you'd like the backup stored.

• If you don't have a domain, create an account with the same username and password on both the machine hosting SQL Agent and the machine hosting the backup files. Change SQL Agent to run as this "role" account, and grant that account permission to write to the directory / share where you'd like the backup stored. (The "poor man's domain"...)

Solution 2:

I totally agree with both answers about UNC path.

I would also like to add that even with mapped drives you have a simple workaround. You can execute a backup to any of normal drives of your server. And then you can add

xp_cmdshell 'XCOPY [source] [destination] \flags'

SQL command to the job or SQL script you run.

With xp_cmdshell you can do even more - for example run an external archive command line tool, like 7z to compress the file before you will copy it to the mapped drive (when remote connection is too slow...)

P.S.: Forgot to mention that xp_cmdshell can be enabled and disabled by using the Surface Area Configuration tool and by executing sp_configure (and by default it is disabled)