Restore Database from a UNC path – Why SSMS doesn’t show network drives/paths in the restore wizard

Lot of times we get into a situation where we need to restore databases from backup files (.BAK) stored in a UNC/network share.

There are two ways we can do this

• By giving the path in the RESTORE DATABASE command like RESTORE DATABASE FROM DISK = \\server_name\shared_drive\backup_file_name.bak
• By giving the path of the file (\\server_name\shared_drive\backup_file_name.bak) in the management studio restore wizard as shown below

One of the big questions asked by lot of people in forums is why we’re unable to explore/locate a UNC path/ a network drive in this wizard.

For e.g. I have 2 local drives C, D and a network drive in my PC and a network drive Z. please find the screen shot below to confirm that.

But I see only C and D in SSMS (SQL Server management Studio) restore Wizard.

Why is this happening? I tried to find the answer through Profiler (the tool that was made in heaven, by god J).

I was doing a restore through the restore wizard, reached the above shown screen which displays the directories and files in it. In the background I invoked profiler trace.

Please find the profiler trace output below

One interesting thing I found is the procedure “dbo.xp_fixeddrives”. This procedure is called in the background when we invoke the restore wizard in GUI. And this procedure get all the available drives, free space available in each drive etc

And if we run it in the query window directly, itgives the below output

If we observe the complete flow,

· First we’re calling xp_fixeddrives

· Getting the names of fixed drives. Here in my case C,D

· Passing them to another procedure called xp_dirtree, one at a time (This procedure takes an argument for e.g. logical drives like C, D and lists all the folders and files present in that drive in a loop)

Below screenshot shows the flow of actions I mentioned above, step by step.

So this procedure xp_fixeddrives can see only the fixed drives on the local computer if we execute it without any parameters.

But if you notices there’s another statement like “xp_fixeddrives 1” which is executed too. This actually lists all the network drives that are mapped to this computer.

But when we execute it in SSMS we won’t see anything. This is because to list the network drives by this procedure the share must be mapped as a network drive in the session in which
SQL Server is running. If you start sqlservr.exe from command line, SQL Server will see any drives you have mapped in your login session. When you run sqlservr.exe as a service, however,
SQL Server runs in a separate session that has no relation to your login session. You need to map the
drive in the session that SQL is running in.

So as a work around, to make network drives visible I did the following steps.

1). Created a directory in the remote computer

2). Shared it with “Everyone” – Actually We just need to make sure that the service account under which SQL is running is having permissions to access the shared folder).

3). Enabled XP_CMDSHELL by issuing

sp_configure 'XP_CMDSHELL' , 1

reconfigure with override

4). Mapped the network drive in the same session as SQL is running by issuing

xp_Cmdshell ‘net use p: \\csm1\bkp_test’

5). Now executed xp_fixeddrives with argument 1

6). The restore wizard now shows network drives

So finally we’re able to see the network drives J

But one caveat here is this will go off once we restart the SQL server. So to prevent that we can create a stored procedure like below and make it as a “Startup Procedure” or procedure that runs on every startup.

CREATE PROCEDURE [dbo].[startupnetmap]

AS

exec xp_Cmdshell 'net use p: \\csm1\bkp_test'

go

And enable it to run at startup by giving

exec sp_procoption 'startupnetmap','startup','true'

So this is why SSMS doesn’t show network drives and this is how we can work around the issue.

Feel free to share your comments and feedback on this.

Regards,

Chandra

Comments

• Anonymous
  March 11, 2014
  Very useful information! I didnt know this trick..pretty cool!

• Anonymous
  August 04, 2014
  sp_configure 'XP_CMDSHELL' , 1 your config is not working on SSMS 201

• Anonymous
  March 28, 2015
  Very useful info.. <a href="http://www.smartdigitizers.com">Thanks!</a>

• Anonymous
  June 22, 2015
  Thank you very much:)

• Anonymous
  December 10, 2015
  Thanks to your informative elaboration! Really Helpful!! Thanks a lot!

• Anonymous
  December 21, 2015
  Assuming the account being used has appropriate permissions to the UNC Share Wouldn't it be easier to just add the full UNC path with the file name in both "Selected Path" textbox and the "File Name" textbox on the "Locate Backup File - yourSqlServerName" Dialog after clicking the add button? Works for me i.e. Open SSMS > Databases > Restore Database... > on the Restore Database dialog select "From Device" click the triple ... > click add button on "specify Backup" dialog > In the: "Selected Path:" textbox enter "\mysharesqlbackupswss_content_intranet.bak" "File Name:" textbox enter "\mysharesqlbackupswss_content_intranet.bak" Then click OK button.... Am I missing something here b/c this process works for me just fine... Thanks. SG.

• Anonymous
  December 21, 2015
  I guess I partial missed the point, with my recommended solution you can't "browse" the UNC like you can any other drive, you have to browse outside of SSMS using explorer and get the full UNC path to paste in... Good point to note the major benefit of the above :)