Introduction
Most of the Development and Test Database Servers will not have enough disk space to store both the database and backup files in order to perform the periodic database refreshes. In such scenarios, the best option will be to store the database backup files in a mapped network drive and perform the database restores from the mapped drive. However, things can become really complicated when the developer tries to restore the database using SSMS and they couldn't locate the respective mapped drive under Locate Backup File window as shown in the snippet below.
In SQL Server Management Studio, if you try to browse the backup files, you will only see the local drives available to SQL Server Database Engine. In this article we will take a look at the approach on How to Configure SQL Server to Display Network Path Visible to SSMS to Perform Database Backup or Restore Commands.
How to Map a Network Drive
By default, a network share is not visible to SQL Server Database Engine. Hence, you will have to mark Network Share as a Mapped Network Drive using the using the Map Network Drive feature of the Operating System. Refer the below screenshots which highlights How to Create a Mapped Network Drive.
Once the Network Drive is mapped successfully the next Step will be to identify the network drive within SQL Server. This can be achieved by using XP_CMDSHELL command. For more information, see How to Enable and Disable XP_CMDSHELL command in SQL Server.
Recommendation: You can't fix an SQL Server Performance Issues until you can identify it. Download Fully Functional Database Performance Analyzer and see how it will pinpoint SQL Server Performance Issue! Become the STAR Performer!
How to Map Network Drive in SQL Server
In the below script replace Drive Letter and Share Path with values in your environment to Map Network Drive in SQL Server.
EXEC XP_CMDSHELL 'net use Z: \RemoteServerNameShareName'
How to Verify Mapped Network Drive Mapping in SQL Server
Execute the below query by replacing the Dirve Letter with the Drive Letter which you have provided in your environment.
EXEC XP_CMDSHELL 'Dir Z:'
Once you have executed the above script successfully you will be to see "Z Drive" under Locate Backup File as shown in the below snippet.
How to Delete Mapped Network Drive Mapping in SQL Server
Execute the below TSQL code to Delete the Mapped Network Drive from SQL Server.
EXEC XP_CMDSHELL 'net use Z: /delete'
Action Item
- You can very well provide the full network location of the file in "File Name" field of "Locate Backup File" window. The approach mentioned in this article is only required if you wish to perform backup or restore using SQL Server Management Studio.
- DBAs or Developers can very well backup or restore database from Network Share without Mapping the drive. This is only required if you wish to perform backup or restore using SQL Server Management Studio.
- Once you have completed the Backup or Restore as a Best Practice on should disable XP_CMDSHELL in SQL Server. For more information see, How to Enable and Disable XP_CMDSHELL command in SQL Server
- Learn more about SQL Server Security Best Practices.