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
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]
exec xp_Cmdshell 'net use p: \\csm1\bkp_test'
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.
Very useful information! I didnt know this trick..pretty cool!
sp_configure 'XP_CMDSHELL' , 1 your config is not working on SSMS 201
Very useful info..
Thank you very much:)