SQL Server Backup or Restore using Network Share with SSMS

written by Ahmad Yaseen January 3, 2025 7 comments

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:

Make Network Path Visible For SQL Server Backup and Restore

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

Resources

Community

Subscribe