SQL Server backup and restore to network drive

By: Greg Robidoux   |   Read Comments (3)   |   Related Tips: More > 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

Last Update: 12/7/2006

About the author

Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips

We Recommend

• More SQL Server DBA Tips...

Comments and Feedback:

Thursday, July 19, 2012 - 11:08:45 PM - I Len

Read The Tip

When I practice on SQL 2005 follow your guide, type in the UNC path in destination driver, Windows show message error: System.Data.SqlClient.SqlError: Cannot open backup device \\fsgserver\d$\autobackup\test-database.bak'. Operating system error 5(Access is denied) I have share full permission for 'everyone' group on folder 'autobackup' but can't connect to those folder. Please help me how can verify and connect to folder backup on other server? Could I contact with you and discuss more detail this problem?. My email is info@ilenlab.com I'm sorry for any inconvenience from this message. Best Regards

Friday, July 20, 2012 - 6:47:35 AM - Greg Robidoux	Read The Tip
I Len - can you check the SQL Server services to see what account is being used for your services.   You may need to use a domain account for the SQL Server servcies instead of the LOCAL SYSTEM account. Try that to see if this helps.  Also, make sure you do this for the SQL Agent account, because this is the account that is used to run the scheduled tasks.

Wednesday, August 29, 2012 - 6:00:24 AM - jithen	Read The Tip
i need to backup a database to VM server where i need to give username and password for the path where i want keep the backup. How can i do this directly from Management Studio? Where should i give username and passwod? Please help me

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis! Get your SQL Server database under version control now! Find out why... What grade do you think your SQL Servers get? Find out with Edgewood's Health Check consulting services. Free SQL Server performance monitoring software! Improve performance by 65% today with IgniteFree. The SQL Server Security THREAT - It’s Closer Than You Think