While taking backups for SQL Server databases onto a mapped drive you might get the following error:
"The system cannot find the path specified."
This is because a network share that you map using a local drive letter will not be visible to a SQL Server instance as it is running as a service. So, the SQL Server service runs in the context of the local console context with the security context of the startup account of SQL Server. Also, mapped drives are specific to a session and not visible to a service started in the local console context.
So, if you want to backup a SQL Server database to a mapped drive using a local drive letter you have the following options:
1. Run the following command from a query window EXEC xp_cmdshell ‘net use <drivename> <share name>’ — where <drive name>: Letter used to map the drive <share name>: UNC path to the share
2. After that you should be able to backup using the mapped drive letter
3. Your Management Studio Object Explorer should be able to list the above drive
Net use documentation:
http://technet.microsoft.com/en-us/library/bb490717.aspx
The drawback here is that once the SQL Server service is restarted, the mapped drive will no longer be visible because it will be unmapped. If you want to persist the mapped drive information then you need to create a startup procedure for executing the script in Step 1.
The easiest way would be to create a backup device using the UNC path of the remote share that you want to take the database backups on. One thing you need to keep in mind that the SQL Server startup account needs to have full permissions on the remote share.
