Problem
Sometimes there is a need to backup or restore your database to another server because of a lack of available disk space on the current server. In this situation you have no other choice but to either remove unneeded files, find additional local storage or backup/restore across the network to another server. When using Enterprise Manager you can only see the local drives, so how do you address the drives on the other server?
Solution
Although SQL Server does not support browsing drives on other servers you do have the ability to use UNC (Universal Naming Convention) notation to address another server on your network. With a UNC path you can specify any server and any share in your network as long as you have access to read and write to these shares.
So instead of using the GUI to browse through the available drives and selecting the directory where you want to write your backup file, you would specify the UNC path for the server such as the following:
Do the following to create a backup:
Click on Add...
Instead of using the ellipses (...) to browse the drives, type in the UNC path along with the file name and select OK.
At this point select OK to start the backup.
In this example the backup file for the Northwind database will be placed on the "F$" share of server "server25". For each logical drive that exists on a server there is a corresponding $ share for each drive. So for example if you had a C, D and E drive you could access these shares as C$, D$ and/or E$. In order to access the administrator shares you need to have administrator access on the corresponding server or grant specific access to use these default shares.
In addition, you can create shares on your server such as SQLBACKUPS which could correspond to a directory such as "F:\SQLServer\Backup". This would enable you to specify the backup for this folder as:
\\server25\sqlbackup\northwind.bak
To do a restore the same process should be followed:
Click on Select Devices...
Click on Add...
Type in the UNC path along with the backup file name and click OK.
Click OK and OK on the next screen to start the restore.
As you can see there is not much too it, but having this ability makes life a lot easier especially in cases where you don't have enough disk space on the local server.
Next Steps
- All of these items of above can also be done using T-SQL commands. Investigate the BACKUP and RESTORE command and how you can also use UNC paths for both backups and restores.
- Take a look at other backup and restore tips on MSSQLTips.com
Last Update: 12/7/2006
About the author
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.
View all my tips