Make Network Path Visible For SQL Server Backup and Restore in SSMS

By:   |   Comments (48)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Restore


Problem

The SQL Server Business Intelligence team at work wanted a copy of the live Data Warehouse database to be restored to their Development SQL Server by replacing the existing database for testing purposes. Checking the Development SQL Server data drive, there was no free space to fit both the database and the backup files. Extending the drive or adding a new drive was not a valid option.  What do I do?

Solution

The SQL Server Business Intelligence team wanted to restore the database using SQL Server Management Studio (SSMS), so the best choice that we found was to use a network drive on another server to restore the database.  When they used SQL Server Management Studio to browse for the network drive they could only see the local drives. In this tip we show how to see other drives to be able to do the restore when using SSMS.

When you try to browse the backup files from SQL Server Management Studio, you will find only the local drives are shown as shown below:

Make Network Path Visible For SQL Server Backup and Restore

Mapping a Network Drive

In order to make a network share visible to SQL Server, it should be mapped as a network drive. First of all, you need to use “Map Network Drive” from the Windows OS as follows to map the network share:

Map Network Drive

Then to identify that network drive in SQL Server, you will use the xp_cmdshell command. Before that, you need to make sure that the xp_cmdshell command is enabled in your SQL instance, as it is disabled by default. Use the sp_configure command to enable it as shown below: 

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO

EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO

Now define that share drive for SQL with the xp_cmdshell command as follows: 

EXEC XP_CMDSHELL 'net use H: \\RemoteServerName\ShareName'

It should now be mapped. In order to verify the new drive, you can use the below command that will show you all files in that newly mapped drive:

EXEC XP_CMDSHELL 'Dir H:'

Let’s try to use SQL Server Management Studio again to browse the path.  As we can see below, we can now see the H: drive:

SQL Management Studio
 

Now that he drive is visible, you can proceed normally with the restore process.

Also you can backup any database to that network path as it is now visible to SQL Server from SSMS.

Delete the Mapped Drive

Optionally you can delete that path after you finish using the below command:

EXEC XP_CMDSHELL 'net use H: /delete'
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Tuesday, June 6, 2023 - 11:04:34 AM - Greg Robidoux Back To Top (91255)
Hi Lavakumar,

writing a backup to a local disk will be a lot faster than trying to write a backup over the network. The only way to improve this would be to make sure the network can handle the traffic and also the storage you are writing to is as fast as possible.

Greg

Monday, June 5, 2023 - 11:17:25 PM - Lavakumar Reddy Back To Top (91253)
Hi,
After mounting the network drive, The backup's performance is very slow from MSSQL DB when moving to the network drive. Kindly assist how to improve/speed the backup's to move to the network drive path.

Tuesday, September 13, 2022 - 11:13:22 AM - Derek Broughton Back To Top (90473)
Surely that first mapping (of a Z: drive) is totally unnecessary. That's mapping a drive for a local user. xp_cmdshell is mapping a drive for a service account.

Thursday, May 12, 2022 - 2:37:53 PM - Faiz Back To Top (90083)
Thanks this is a very useful article and very well explained.

Friday, July 2, 2021 - 12:38:26 PM - Carlos Batista Back To Top (88938)
Thank for this, very helpful

Monday, February 15, 2021 - 11:26:13 AM - Mehradad Back To Top (88241)
Create and use Network Drive for your shared folder instead.

Tuesday, December 29, 2020 - 6:16:43 AM - Azeez Back To Top (87964)
Very useful command and very helpful in terms of using the network drive

Thursday, May 14, 2020 - 9:43:43 AM - Patrick Back To Top (85655)

Hi Greg,

Thanks for the help

Thursday, May 14, 2020 - 9:21:50 AM - Greg Robidoux Back To Top (85654)

Hi Patrick,

not sure how long the mapped drives stay connected or what casues them to disconnect.  One thing you could do is create a SQL Agent Job to run each day to do the mapping, this should help with the issue.

-Greg

Wednesday, May 13, 2020 - 4:07:43 PM - Greg Robidoux Back To Top (85647)

Hi Randy,

one way you can do this is to create a startup stored procedure to map the drive.  So whenever SQL Server restarts the drive gets remapped.

https://www.mssqltips.com/sqlservertip/1574/automatically-running-stored-procedures-at-sql-server-startup/

-Greg

Wednesday, May 13, 2020 - 2:41:13 PM - Randy Back To Top (85646)

Its interesting but even with the /persistent:yes, it keeps losing the connection after the SQL service restarted. So this is not persistent. How can i prevent the mapping from disapearing after a server or service restart? I tried this on several servers all have this problem. 

Monday, May 4, 2020 - 3:48:06 PM - Patrick Back To Top (85565)

Hi,

Thanks, it worked, but somehow, after 2 weeks it stopped working and I had to recreate the map drives.  What's the lifecycle for these drives to stay alive?

Wednesday, January 29, 2020 - 5:52:09 PM - ADesai Back To Top (84041)

Worked like a charm for my servers across AD OUs!  Thank you for sharing! :)

Thursday, July 18, 2019 - 11:59:30 AM - Demetrio Back To Top (81790)

Excellent post. It's been really helpful.

Wednesday, July 17, 2019 - 2:52:33 AM - Suman Roy Back To Top (81774)

Excellent post, helped me a lot. Thanks

Saturday, June 8, 2019 - 9:45:02 AM - Faizan Qureshi Back To Top (81378)

thanks for your post

Saturday, February 23, 2019 - 10:18:19 AM - Jason Lasby Back To Top (79104)

This process has changed slightly, this method worked great then I updated my SQL Server and then the mapped drives dissapeared and I couldn't get them back no matter what I did to execute this again, you now need to include the username and password when setting it up!

Just change the
EXEC XP_CMDSHELL 'net use E: \\ipaddress\share' 

to

EXEC XP_CMDSHELL 'net use E: "\\ipaddress\share name\share name" /user:domain\username password /persistent:yes'  

Also note the use of double quotes around the share path, regardless if I had spaces in the name or not, I was getting path not found errors without them!

Wednesday, February 6, 2019 - 5:20:13 AM - Vilhelm Yngvi Kristinsson Back To Top (78974)

Thanks!

Wednesday, January 16, 2019 - 8:35:24 AM - pralhad Back To Top (78784)

 Its really useful !!! Keep posting and sharing good things !!!

Pralhad.

Wednesday, January 16, 2019 - 8:28:18 AM - pralhad Back To Top (78782)

 Hi Ahamad,

This is really helpful and resolve my needs .

Thanks a lot , keep posting.

Thanks,

Pralhad.

Monday, June 25, 2018 - 9:28:12 PM - John Back To Top (76397)

 

 This is great! Thank you -- I wanted to add something though, if you're share has a space in it, you will need to encapsulate the path in double-quotes like so:

EXEC XP_CMDSHELL 'net use Z: "\\RemoteServer\Shared Drive" MyPassword /User:Domain\LoginName'

Friday, June 1, 2018 - 11:07:08 AM - Youssef Back To Top (76072)

 

 thank you very much it helps me a lot

Tuesday, October 17, 2017 - 2:08:10 AM - Ahmad Yaseen Back To Top (68464)

 

 Thank you Naval for your comment.

Please make sure that don't face any issue while mapping the drive from the windows side. If all is OK with that please make sure that the SQL Server service account has access on that shared folder.

 

Best Regards,

Ahmad

Tuesday, October 10, 2017 - 6:47:19 AM - Naval Back To Top (67149)

 

 

 An error occurred while executing batch. Error message is: The directory name is invalid.

 

THIS MY MY ERROR 

 

EXEC XP_CMDSHELL 'net use Z: \\metroshareddata.fease.case.windows.net\azureshareddata\shareddata\STATUSPOINT\DEV\DailyDBSync/USER:.\metroshareddata password'

above is my command  and i dont know where i make mistake

 

Monday, October 2, 2017 - 4:11:20 AM - Andre Nel Back To Top (66786)

 Hi,

 

When i run the last command where you set the network path it asks me to put the password in for the user for my share

 

Thursday, July 6, 2017 - 10:33:39 AM - prakash Back To Top (58962)

 

 HI , 

 

 thx , its working for me . mainly have to give credentials(User name and PWD) in XP_cmdshell  . otherwise it wont work 

Wednesday, June 21, 2017 - 4:56:10 PM - Norsmith Back To Top (57881)

Thanks a lot!

 

...Now define that share drive for SQL with the xp_cmdshell command as follows:

 

EXEC XP_CMDSHELL 'net use H: \\RemoteServerName\ShareName' ...

I would add the   .../user: ' to that string. And I would note that good to have the same user on SQL and on you network storage machine

Monday, October 10, 2016 - 4:09:35 AM - Martin Back To Top (43521)

 I was able to do the enable procedure without errors but can´t use the exec xp_cmdshell command. After trying different things I found, that the command is case sensitive on this server so it works with EXEC xp_cmdshell instead of EXEC XP_CMDSHELL.

Thanks for the post!

 

Wednesday, August 3, 2016 - 10:30:47 AM - Rhayader Back To Top (43043)

GREAT tip, this is a huge help if you only have SQL Mgmt Studio access to your server, and can't reach it via RDP or SMB.

 

As Len mentioned upthread, with a default SQL server setup you will probably also have to pass domain credentials through with that net use command, for authenticating to your desired share.  The correct format there would be:

 

EXEC XP_CMDSHELL 'net use H: \\servername\sharename /user:domain\username password'

Tuesday, July 5, 2016 - 8:11:40 AM - Ahmad Yaseen Back To Top (41815)

 Dear Rioshalom,

Please make sure that the path you are trying to use is a shared path and that the SQL accoun that is running the backup has read and write access on that folder.

 

Regards,

Ahmad

 

Saturday, July 2, 2016 - 8:32:32 AM - Roishalom Back To Top (41806)

 Dear Sir,

I ran the SQL you gave to map a network dirve.

Below error occur after runing the SQL;

      "Output
      System error 53 has occurred.
      NULL
      The network path was not found.
      NULL
      NULL"

NB: Kindly, look at and advice me futher.

 

regards,

 

Thursday, June 30, 2016 - 11:54:56 AM - CK Back To Top (41797)

 Hi there,

The overall objective that I am trying to accommplish is to have data from three local terminals each with custom SQL database to populate a SQL database (same structure) located on a mapped network drive.  A custom software tool on each terminal points to the local database and upload target database.  Just as others have done after researching the web, I have managed to do the following:   

-Enable tracer1807

-XP CMDSHELL procedure to enable SQL to see external drives

-XP CMDSHELL procedure to map the P: drive successfully (needed domain user name and password)

Now how does one go about creating the database on that mapped P: drive?  Does the path have to have the exact identical description (...MSSQL11.........) as described above in your example of mapping drive or can it be just a path into a generically named folder?  Since I do not have an inkling of software jargon, I request a very descriptive answer if possible.

Thanks in advance

 CK

Saturday, June 4, 2016 - 10:37:13 AM - Mohammed Qadeer Back To Top (41610)

 Hi..all

Thank you for this valuable post..

Here, I have a little change.. my requirement is, I doesn't want to map the network drive... I simply want to use this below command directly in SQL-Script:

net use \\10.0.1.1\SQL_Backups /user:admin admin

i.e. net use \\NetworkDrive\Folder(withPassword) /user: username password

I tried almost all syntax etc and read many blogs but unable to fit this one line in SQL-Script.

Is this possible? or can you show me how to use IF statement in SQL-Script?

 

Thanks in advance,

Regards,

Mohammed

 

Thursday, April 28, 2016 - 8:47:32 AM - Craig Gall Back To Top (41360)

Hi all,

 

I would like to try and use this to help backup an SSAS cube to a network share.

Despite running i cannot see my network share as an available location to backup my Cube.

 

 

Thursday, May 7, 2015 - 9:10:23 AM - sina Back To Top (37119)

Hello mr mohammad.

after use xp_cmdShell to transfer backup file to a shared network path (xp_cmdShell use xcopy command to transform backup file)

when is completed.

when i want to resotre backup file on destination server give me this error:

Error 3242: the file on device .... is not a valid microsoft tape format backup set.

RESOTRE FILELIST is terminating abnormally.

please help me

thanks

Wednesday, March 4, 2015 - 8:45:19 AM - Ahmad Yaseen Back To Top (36443)

Thanks All for your comments.

 

Dear mohamad,

 

we should disable it directly after we finish as it is a security risk. The main reason is the context in which xp_cmdshell runs and what it gives access to.

 

Best Regards,

Ahmad

Wednesday, March 4, 2015 - 2:51:42 AM - mohamad Back To Top (36438)

Is ٓXP_CmdShell should always be active?

Wednesday, March 4, 2015 - 12:16:28 AM - Ahmad Yaseen Back To Top (36437)

Thank you all for your comments.

 

Dear Shawn,

You have the choice to change the SQL account to Local SYSTEM which will use the (domain\servername$) user and grant it access to the shared folder or grant (EveryOne) user access to that shared folder if this backup and restore process is temp action.

 

Regards,

Ahmad Yaseen

Tuesday, March 3, 2015 - 7:26:43 PM - Joe Torre Back To Top (36433)

It might be worth mentioning, one can restore from a network share as well, without having to map a drive.

Tuesday, March 3, 2015 - 7:00:08 PM - Len Back To Top (36432)

If your SQL service is not running on a domain account, which is how you would give the Share permission to let SQL read/write, you can still use this tip; however the command gets a little longer: Get the account and password you need and convert your command ...

FROM:
EXEC XP_CMDSHELL 'net use H: \\RemoteServerName\ShareName'

TO:

EXEC XP_CMDSHELL 'net use H: \\RemoteServerName\ShareName **PASSWORD** /USER:RemoteServerName\AcctNameHere'

Additionally, you can see what network shares SQL current has with this command:

EXEC XP_CMDSHELL 'net use '

Tuesday, March 3, 2015 - 4:12:39 PM - Ravi Back To Top (36428)

Great Info. This could be a life saver!!

Thanks

 

Tuesday, March 3, 2015 - 3:08:57 PM - Ben Back To Top (36425)

You can also just paste the UNC path into 'selected path' and the filename into 'file name' and it will restore from a network location.

Tuesday, March 3, 2015 - 12:25:57 PM - Brittany Back To Top (36423)

Great post!

If the BI team also knew the name of the file, they could enter the full network location of the file in the 'File Name' field within the 'Locate Backup File' window without having to map the network location.

Tuesday, March 3, 2015 - 11:47:19 AM - Eric Back To Top (36422)

Another way to reference a drive on a different server (e.g., the F drive on nts-server01) is to set up a backup device on the development server called "from_PROD" using a URL like the following as the destination:

\\nts-server01\f$\MSSQL\backup\DMP_PROD.BAK

When you want to restore from production to development, you reference the from_PROD backup device as the source.

Tuesday, March 3, 2015 - 11:00:24 AM - mpereira1 Back To Top (36421)

That's useful information.  Thanks for the tip!

Tuesday, March 3, 2015 - 10:56:10 AM - paul lee Back To Top (36420)

 

Need to enter user name and password on the net user command line, otherwise will give errror on SSMS

Tuesday, March 3, 2015 - 10:37:37 AM - Al Noel Back To Top (36419)

Thanks. Never knew you could do this.

Tuesday, March 3, 2015 - 8:58:58 AM - Shawn Back To Top (36417)

I have found this solution to be challenging if you are not running the SQL Server database service with a domain account that has permissions to the remote server share.   How do you modify the provided solution to work in the event you are using a local account to run the SQL Server database service (SYSTEM, NT SERVICE\MSSQLSERVER)?   What am I missing?    Thank you, Shawn














get free sql tips
agree to terms