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

By: Ahmad Yaseen | Comments (48) | Related: 1 | 2 | 3 | 4 | 5 | 6 | More > 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:

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:

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

About the author

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

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

Hi Greg,

Thanks for the help

Thursday, May 14, 2020 - 9:21:50 AM - Greg Robidoux

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

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

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

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'

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

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

Pralhad.

Wednesday, January 16, 2019 - 8:28:18 AM - pralhad

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

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

thank you very much it helps me a lot

Tuesday, October 17, 2017 - 2:08:10 AM - Ahmad Yaseen

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

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

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

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

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

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

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

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

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

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

Saturday, June 4, 2016 - 10:37:13 AM - Mohammed Qadeer

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

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

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

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

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

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

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

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

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

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

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

Problem

Solution

Mapping a Network Drive

Delete the Mapped Drive

Next Steps

About the author

Comments For This Article

Related Articles