Table of Contents
Problem
When you try to browse the backup files from SQL Server Management Studio, you will find only the local drives are shown as shown below:
Mapping a Network Drive
In order to make a network share visible to SQL Server, it should be mapped as a network drive. First of all, you need to use “Map Network Drive” from the Windows OS as follows to map the network share:
Map a shared folder into a drive into your SQL server.
Enable mapped drive in SQL server
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 Z: \\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 Z:' 
Let’s try to use SQL Server Management Studio again to browse the path. As we can see below, we can now see the Z: drive:
Now that he drive is visible, you can proceed normally with the restore process. Also you can backup any database to that network path as it is now 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 Z: /delete'
