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
you can use SQLCMD to do this and run BACKUP commands from SQLCMD. You should be able to run the script from the A server, but the A server will need to be able to see the B server. Also, since they are in different domains it will be easier to use Standard SQL logins instead of Windows logins.
I had two servers(SQL 2005) A and B. B is Production server and A is DR. I want to take backups for B server databases from A server. We can not create any maintenance plans or jobs in B production server. We can create any jobs in DR server to take backups for Prod server. Is it possible? both are in different domain. If the servers are in same domain is it possible? and please send the procedure. Please help me asap.
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
Friday, July 20, 2012 - 6:47:35 AM - Greg Robidoux
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.
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.
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.
