Configure Network Drive Visible for SQL Server During Backup and Restore Using SSMS

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.

This article will help you understand How to Backup SQL Server Databases to a Mapped Drive (Backup to Network Drive). There are times when you need to take SQL Backup to Network Dive or even use SSMS to take backup of SQL Backup Database to Network Drive. Learn How to Give SQL Server Access to Network Share.

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.

How to Map Network Drive in SQL Server | SSMS Backup Database to Local Disk

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: \\RemoteServerName\ShareName'

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'

Trending SQL Server Tips

• SQL Delete Duplicate Rows from a SQL Table in SQL Server
• How to Configure an SPN for SQL Server Site Database Servers
• Register a Service Principal Name for Kerberos Connections
• How to troubleshoot the “Cannot generate SSPI context” error message
• How to use Kerberos authentication in SQL Server
• Download Microsoft Kerberos Configuration Manager for SQL Server – A diagnostic tool that helps troubleshoot Kerberos related connectivity issues with SQL Server and SQL Server Reporting Services

Recommendations

• 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.