There is no need to map a drive in order to put a backup on remote storage. The BACKUP DATABASE command will directly accept a UNC path
BACKUP DATABASE DB1 TO DISK='\\192.168.1.93\share\db1.bak'
Here are a few benefits of directly using a UNC path:
NOT a good idea to execute commands through xp_cmdshell that can trigger an error. For example, let's say that the Z: drive has already been mapped or that \192.168.1.93\share is unavailable. How do you handle those situations?
Directly using a UNC path: if the path is unavailable, the BACKUP DATABASE command will throw a trappable error.
Security: If you grant the SQL Server Service Account the ability to see the share and read/write to the directory, that's all you need. With an xp_cmdshell implementation, you are forced to hard-code a user account and password. Not only is this NOT secure, it's a maintenance problem waiting to happen. What do you do when the password has to be changed?
commented on Jan 5 2012 12:01AM