How to backup SQL Server databases to a mapped drive

  • Article

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 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 logical drive enumeration window should be able to list the above mapped drive

Net use documentation:

https://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.

Ideally, services should not access any Local or Network resources through mapped drive letters. Since the behavior of mapped drives differ across versions of Windows, the above mentioned approach may NOT work in all scenarios. Please refer the following KB Article for more details: 180362 INFO: Services and Redirected Drives ( https://support.microsoft.com/default.aspx?scid=kb;EN-US;180362 )

 

Amit Banerjee
Technical Lead, Microsoft Sql Server.