Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

SQL Server backup and restore to network drive

MSSQLTips author Greg Robidoux By:   |   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
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server 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



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.