How To Restore/Backup SQL Database From/To Remote Server

Did you ever need to restore a SQL database from a backup (.bak) file which is located on another remote server? Or let's be more general, did you ever need to access a file from SQL Server Management Studio File Explorer and this file was located on a remote server?

Some may think that this could be achieved by creating a mapped network drive, on the machine having SQL server installed, which is pointing to the shared path in the other remote server...... then this mapped network drive would be accessible from SQL Server Management Studio File Explorer.

Alas, even after creating the mapped network drive this drive will not be accessible from SQL Server Management Studio File Explorer, actually it will not appear among the available drives.

So, how to do it? If you are interested to know, you can read the rest of the post which will explain in details and screenshots how to do it.


Problem???!!

1. We have a "DB.bak" file which is a database backup file
2. It is located on a server named "ServerName" (this server may be a web server, file server or any type of servers with file system, it doesn't need to be a SQL server at all so don't get confused)
3. We have another SQL server on which we want to restore the "DB.bak" file
4. When we proceed with the regular DB restore operation, we can't browse to the "DB.bak" file from SQL Server Management Studio File Explorer and we can't enforce it by anyway
5. We need to make the "DB.bak" file accessible from SQL Server Management Studio File Explorer

Configuring Security & Sharing on the Remote Server (ServerName)

On the remote server called "ServerName" which has the "DB.bak" file on its file system, browse to the folder including the file, right-click, properties and then follow the screenshots below.

Creating Mapped Network Drive on the SQL Server

Log on the SQL server machine to create a mapped network drive which is mapped to the shared path you already created in the previous step. To do this, follow the screenshot below.

Registering the Mapped Network Path on SQL Server Management Studio

You, you need to register the mapped network path on the SQL Server Management Studio in order to be able to access the mapped drive from SQL Server Management Studio File Explorer. To do so, open SQL Server Management Studio and execute the query below.

EXEC sp_configure 'show advanced options', 1  
GO  
RECONFIGURE  
GO  
EXEC sp_configure 'xp_cmdshell', 1  
GO  
RECONFIGURE  
GO   

EXEC XP_CMDSHELL 'net use N: /delete'  
EXEC XP_CMDSHELL 'net use N: \\ServerName\DB-Backup-ShareName'  
EXEC XP_CMDSHELL 'Dir N:'

Result???

Now, you can find the mapped network drive (N:\ in our case) among the other drives in the SQL Server Management Studio File Explorer. Now you can restore/backup from/to the shared path as you wish.


Wish this may help you some day, good luck :)