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?
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;
EXEC sp_configure ‘xp_cmdshell’,1
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>