2

I found a lot of similar questions in the archives, but none quite answered this.

I need to copy a database from one server (SQL Server 2008) to another. Each server is in its own system, and no computer can connect to both servers.

My original plan was to backup the database, copy it to an external hard drive, move it to the new server, and restore. Unfortunately, the server with the database is entirely out of storage, preventing me from making the backup file. Is there a way to backup a database from a remote server directly onto an external hard drive, or onto the local computer? When I use the backup wizard, it only shows me local paths.

Is there another method I should be thinking of using?

Thanks very much.

Geoff
  • 21
  • 1
  • 2
  • Thanks for the responses. I wasn't logged in when I posted my original post. Too early for me too, I guess. But I am the original poster. I am attempting to back up to a local path, as discussed, but I'm not sure if I have the path name right. I have tried the following: `\\localhost\e$\data\backup.bak` `\\127.0.0.1\e$\data\backup.bak` And neither works. I know this is a basic question, but how do I find my external drive's UNC? Thanks again! – registrar Jun 08 '11 at 15:35
  • @Peter - I'm not sure that's the problem. The database is hosted on a remote server, and the hard drive is mapped on my local computer. My problem is that I don't know how to specifiy a local address for the backup, and the hard drive is local. If the hard drive were attached to the server, I don't think it would be a problem. My current approach is as follows: right click on the database > Tasks > Back Up...; under "destination," I click "add..." and type in: `\\localhost\c$\backup.bak` And it throws the following error: "cannot verify the existance of the backup file location" Thanks again f – registrar Jun 08 '11 at 16:51

4 Answers4

3

You can detach the database, copy the data (.mdf) and log (.ldf) files to external storage, then reattach those files on the new server. When you detach a database, SQL Server closes the data and log files and the database is removed from the server. You can then work with the files safely. If you want to retain the database on the original server you just reattach the database.

For instructions on how to do this via SQL Server Managment Studio see this article.

For instructions on how to do this via T-SQL see this article.

squillman
  • 37,883
  • 12
  • 92
  • 146
2

You cartainly can backup a database to a UNC path. When you're selecting the backup destination (when you select the disk option in the backup) simply type the UNC path that you want to backup to, including the filename, such as:

\\server\share\database.bak
joeqwerty
  • 109,901
  • 6
  • 81
  • 172
  • I assumed this would not be an option since OP states that no computer can connect to both servers. – squillman Jun 08 '11 at 12:31
  • True, but the target for the backup doesn't have to be the destination server for the database. The OP can backup the database to a UNC path that's local to the source server, then simply copy the backup from the backup target machine to an external hard drive and transport it to the destination server. I assumed that the OP meant that no computer can establish a connection to both computers simultaneously (different networks) but that he has at least one computer at each location that can communicate with the relevant server. I could be misunderstanding things. – joeqwerty Jun 08 '11 at 12:36
  • Yes, of course... [back for more coffee] :) – squillman Jun 08 '11 at 13:02
  • Me too... too early... – joeqwerty Jun 08 '11 at 13:34
1

On your local machine, (The "admin" box that has the external hd plugged in) share a folder on the external hd with "everyone", full rights.

On the remote server, start:run, type \adminbox\newsharename

Once you can browse that unc from your rdp session on the remote db server, you'll be able to back up to it using sql studio.

Bob
  • 597
  • 2
  • 8
0

If you can RDP onto the server, then you can obviously find out the details from Windows Explorer.

If you can't, execute xp_fixeddrives in Query Analyser - and hopefully, the USB drive with the available disc space will stick out like a sore thumb. You should then be able to backup to it like a regular drive.

Peter Schofield
  • 1,639
  • 9
  • 11