Microsoft SQL Server Manager | Allow network drives for Restore and Backup

Being forced to work with the MS SQL Server Manager is very tiring. Most time I can accomplish all the tasks using the IntelliJ Database Tools but sometimes there is no way around Microsoft management tool.

Problem: No network disks available

One thing that always annoyed me was the missing access to network drives while restoring or backing up a database. The list provided only the physical hard drive and ignores all the network drives configured in my explorer. 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.

Solution

At first I copied the backup files to the machine running the sql server but this was a very nerving work around.
To make the network drives available in the SQL Server Management Studio you have to go trough the following steps:

1. Map a network drive using windows that should be visible for the SQL Manager. You are able to create/connect a network drive using a path in you network using the windows explorer window.
2. To make the newly created network drive available in for the SQL Server, we have to use the xp_cmdshell command. You need to make sure that the command is enabled in your SQL instance (it's disabled by default). To enable the command you have to execute the following script
3. as a query in your management studio:
    
    
   -- To allow advanced options to be changed.
   EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO

1. Now we can use the enabled xp_cmdshell command to tell SQL about the network drive and to verify the accessability:

   -- Create the shared drive for SQL
   
   EXEC XP_CMDSHELL 'net use W: \\remotePath'
   
   
   
   -- Verify that the added network drive is accessible
   
   EXEC XP_CMDSHELL 'Dir W:'

   Thats's it! Now you should be able to access the network drive within the SQL SERVER Manger

Removing the network drive

If you would like to remove the mapped network drive you can use the following command:

EXEC XP_CMDSHELL 'net use H: /delete'