Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (26)   |   Related Tips: More > Restore

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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' /pre>
Next Steps


Last Update:


signup button

next tip button



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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

 

 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

 

 

 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 02, 2017 - 4:11:20 AM - Andre Nel Back To Top

 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 06, 2017 - 10:33:39 AM - prakash Back To Top

 

 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

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

 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 03, 2016 - 10:30:47 AM - Rhayader Back To Top

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 05, 2016 - 8:11:40 AM - Ahmad Yaseen Back To Top

 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 02, 2016 - 8:32:32 AM - Roishalom Back To Top

 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

 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 04, 2016 - 10:37:13 AM - Mohammed Qadeer Back To Top

 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

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 07, 2015 - 9:10:23 AM - sina Back To Top

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 04, 2015 - 8:45:19 AM - Ahmad Yaseen Back To Top

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 04, 2015 - 2:51:42 AM - mohamad Back To Top

Is ٓXP_CmdShell should always be active?


Wednesday, March 04, 2015 - 12:16:28 AM - Ahmad Yaseen Back To Top

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 03, 2015 - 7:26:43 PM - Joe Torre Back To Top

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


Tuesday, March 03, 2015 - 7:00:08 PM - Len Back To Top

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 03, 2015 - 4:12:39 PM - Ravi Back To Top

Great Info. This could be a life saver!!

Thanks

 


Tuesday, March 03, 2015 - 3:08:57 PM - Ben Back To Top

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 03, 2015 - 12:25:57 PM - Brittany Back To Top

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 03, 2015 - 11:47:19 AM - Eric Back To Top

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 03, 2015 - 11:00:24 AM - mpereira1 Back To Top

That's useful information.  Thanks for the tip!


Tuesday, March 03, 2015 - 10:56:10 AM - paul lee Back To Top

 

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


Tuesday, March 03, 2015 - 10:37:37 AM - Al Noel Back To Top

Thanks. Never knew you could do this.


Tuesday, March 03, 2015 - 8:58:58 AM - Shawn Back To Top

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


Learn more about SQL Server tools