SQL Server Backup or Restore using Network Share with SSMS

Problem

The SQL Server Business Intelligence team 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.  In this article, we look at how to create a SQL backup to network share or restore a backup from a network share.

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.

Browsing for backup files in SQL Server Management Studio only lets you see the local drives:

Mapping a Network Drive

Make a network share visible to SQL Server by mapping 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 a SQL database to the network share now that it is 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'

Next Steps

• Review SQL Server Backup Tips
• Review SQL Server Restore Tips
• Read more about XP_CMDSHELL

Ahmad Yaseen

Ahmad has a Bachelor’s Degree in Computer Engineering from the University of Jordan and five years of experience working as a SQL DBA, gaining valuable knowledge of database structures, practices, principles and theories. His experience also includes.NET development, working with database applications, scripting and creating SQL queries and views. His personal abilities include having very strong communication and interpersonal skills, the ability to prioritize and to make good sound decisions that benefit the company. He has experience in upgrading, configuring, securing, tuning and monitoring SQL Servers since SQL Server 2005. This includes SQL Server performance tuning, SQL Server resource governor management, SQL Server maintenance plans, SQL Server data collection (Reports) analyzing and SQL databases design, developing, indexing and query optimization. In addition, he is familiar with installing and configuring SSRS, SSIS and SSAS. When it comes to disaster recovery and high availability, he has a solid foundation in SQL backup and recovery scenarios, mirroring, replication, log shipping, SQL clustering and AlwaysOn technology.

• MSSQLTips Awards: Author Contender – 2016-2017 | Trendsetter (25+ tips) – 2016 | Rookie Contender – 2015