By: Greg Robidoux | Comments (9) | Related: > Backup
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
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
