SQL Server Articles, Tips, FAQs, Reviews, Whitepaper, News etc - MyTechMantra.com

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.

Locate Backup Files in SQL Server

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.

How to Map Network Drive in SQL Server
How to Map Network Drive in SQL Server
Specify Drive Letter for Network Drive and Share Folder Path to Map Network Drive as Local Drive
Specify Drive Letter for Network Drive and Share Folder Path to Map Network Drive as Local 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

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.

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

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'

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

Ashish Mehta

Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Newsletter Signup! Join 15,500+ Professionals




Be Social! Like & Follow Us...

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Recent SQL Server Tips