Freigeben über

How to backup SQL Server databases to a mapped drive

  • Artikel

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.

Comments

  • Anonymous
    February 26, 2009
    PingBack from http://www.clickandsolve.com/?p=15578

  • Anonymous
    April 30, 2009
    It works perfectly with single instance on the server. But when I have several instances and I would like to use this method to the same mapped drive I get the error of "The local device name is already in use". Is there a work around it?

  • Anonymous
    April 30, 2009
    The error (operating system error #85) that you are referring to would occur only if you try to re-map a UNC path to the same drive letter under the same security context. Before you perform backups on the mapped drive for the first time after a SQL Service restart, you would have to map the drive using xp_cmdshell. Or if you want to avoid this step, then you would have to add a startup proc which performs the drive mapping. This has to be done for each instance present on the server only ONCE after a SQL Service restart. Could you provide the Operating System and SQL Sever version details? When you map the drive for the first time, make sure you pick a drive letter which is not currently being used. I have verified that on a server with multiple instances, I can map the same drive letter using xp_cmdshell on all three instances to the same UNC path.

  • Anonymous
    May 01, 2009
    It didn't work for me, everything went through without error and I can see the mapped drive in Windows Explorer(listed as a disconnected drive...but I can see the files).  I still can't see the drive when I try to back it up...any advice?

  • Anonymous
    May 03, 2009
    If NET USE command throws an error while mapping it, xp_cmdshell wouldn't report an error. If you perform a EXEC XP_CMDSHELL 'dir <drive name>', you should get an output. If you don't and it throws back an OS error in the output window, then we would need to troubleshoot that error. If this gives you a directory listing, then you should see the drive in the Object Explorer when specifying a device location to perform the backup. HTH

  • Anonymous
    April 16, 2010
    What is the error that you are receiving and what version of SQL are you trying this on? I have already tried this on my server. I have already mentioned that "services should not access any Local or Network resources through mapped drive letters" but this is a workaround if you have no other choice.

  • Anonymous
    June 27, 2011
    Here is perfect example EXEC XP_CMDSHELL 'NET USE Y: "\network pathD$Microsoft Sql Data" /user:rack.comalondhe password'

    • Anonymous
      August 22, 2016
      Perfect. Thanks

  • Anonymous
    July 05, 2011
    Very useful article, wish I've found it before when I got this problem. However, I solved the problem with this tool http://www.sqlbackupandftp.com. It allows you to run remote backups on your LAN and FTP sites as well.

  • Anonymous
    September 25, 2013
    This worked perfectly and helped me a lot.  Thank you.

  • Anonymous
    December 04, 2013
    Wow. Thank you so much. I have been reading various Microsoft articles about mapping to network drives, but none of them mentioned to do this before dealing with the domain account which SQL Server service uses to log on. I had to enable the xp_cmdshell advanced options (read technet.microsoft.com/.../ms190693.aspx to do this), but as soon as I did that I was able to run the above query and my mapped network drive was then seen by SQL Server as a viable path to store my .bak files. Awesome.

  • Anonymous
    January 06, 2014
    Thank you so much, this was very helpful.

  • Anonymous
    March 07, 2014
    Amit, I'm trying to set up a second backup (actually restore to) location.  I've set it up just as you've described before.  This time after I map the drive and try to DIR the share via xp_cmdshell, it gives me "access denied".  I've set up the share and given full control to my windows login.  I'm logged into SSMS with my windows login, so xp_cmdshell must be using another account.  How do I go about using another account with xp_cmdshell?

  • Anonymous
    January 16, 2015
    i try command work successfully (exec xp_cmdshell 'net use Y: \ my path) but it still not showing mapped dr i m using sql server2008r2 thanks for posting

  • Anonymous
    September 28, 2016
    Thanks