I have a SQL Server (2000, 2005 and 2008) and I'd like to use SQL Agent (or even a simple backup database 'xxx' to disk = 'yyy'), to backup to remote drives.
I.e: i have a mapped drive in the SQL machine, for example: "M:" which maps to \\otherbox\someshare
By default SQL Server won't allow you to backup to such drives, but I think that there's a way to enable that. Can somebody point me to the docs?
Thanks in advance.
Best Answer
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"...)