There’s a lot of times when people need to restore databases from a network (UNC) path.  There are two ways of doing this:

  • By giving the path in the RESTORE DATABASE command like:
    RESTORE DATABASE FROM DISK = \\server_name\shared_drive\backup_file_name.bak
  • By giving the path of the file (\\server_name\shared_drive\backup_file_name.bak) in the management studio restore wizard

However, using SQL Management Studio (SSMS), those networks paths aren’t available in the restore wizard.  The reason is because a procedure “dbo.xp_fixeddrives” is called in the background when the load the restore wizard in SSMS.  This procedure retrieves all of the available drives along with the free space available on each drive.

xp_fixeddrives

Then, the SQL Management Studio restore wizard calls another procedure “dbo.xp_dirtree” passing each drive letter found.  For all results returned, the procedure is recursively called to create a directory tree in the wizard.

xp_dirtree

As you can see, “dbo.xp_fixeddrives” only returns drive letters for logical drives attached to the system – not network (UNC) paths.  Thus, why we can’t see (and therefore restore from) UNC paths.

In order to see network drives, the share must be mapped as a network drive in the session in wich SQL Server is running. If you start sqlservr.exe from the command line, SQL Server will see any drives you have mapped in your login session.  But, when you run sqlservr.exe as a server, SQL Server runs in a separate session that has no relation to your login session.

  • To fix this temporarily, you can map the network share within SSMS for your current login session.
  • To fix this permanently, you need to map the drive in the session in which SQL is running.

Map Network Share Within SSMS for Current Login Session

  1. Make sure you have a directory available on the public share
  2. Regarding permissions, either share the remote directory/folder with “Everyone” or the domain identity in which the SQL service is running
  3. Enable XP_CMDSHELL by issuing: 
    exec sp_configure 'xp_cmdshell',1
go
reconfigure with override
go

    NOTE: If running the above returns: The configuration option ‘XP_CMDSHELL’ does not exist, or it may be an advanced option. Run the following first, then run the above commands again:

    exec sp_configure 'show advanced options', 1
go
reconfigure
go

     

  4. Map the network drive in the same session as SQL by issuing: 
    xp_cmdshell 'net use z: \\server_name\drive_name'

     

  5. Now executing “dbo.xp_fixeddrives” with an argument of 1 returns:
    xp_fixeddrives1
    Which shows us out network drives.
  6. The restore wizard also shows our network drives:
    ssms_restore_wizard

Now, we’re able to restore from our network drives (UNC paths).

Keep in mind, however, that this ability will be removed once we restart SQL server. So to prevent this we can create a stored procedure like below and make it a startup procedure for the SQL service. This way, all of the above is done for you every time SQL starts (such as machine reboots, etc.)

Map Network Share Permanently in SSMS

  1. Create a stored procedure: 
    CREATE PROCEDURE [dbo].[mapdrives]
AS
    exec xp_cmdshell 'net use z: \\server_name\drive_name'
GO

     

  2. Then, enable that stored procedure to run at startup: 
    exec sp_procoption 'mapdrives','startup','true'

     

Now, the stored procedure will run at startup and the UNC paths will always be available in SSMS.