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:
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:
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.
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
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.
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?
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'
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
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'
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.
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'
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.
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
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
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.
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.
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'
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.
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:
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
Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.
