Copying data to another site 1

[oblib]

Can someone advise me on the best way to take copies of a database at one site to be used at another site?

 

[JayKusch]

A couple ways come to mind.

Surest Safest way would be to run a backup and restore of the database.

Second option would be, if you can get exclusive rights to the database and it is not a 24/7 production DB or a DB that cannont go offline is to "Detach" the database, copy the associated MDF and LDF(s), which are your databases Data file and Log file(s). I say Log files in that some DBA setup multiple log files in their environment but in "most" cases you will just have one MDF and LDF file. You will then copy the MDF/LDF files to their new locations and then run an Attach DB command to re-establish the original DB you are making a copy of and then you will run the Attach DB at the new target site to bring the DB online there.

You can Detach/Attach the DB by using a SQL stored procedure called "sp_detach_db" and then "sp_attach_db" or "sp_attach_single_file_db" if your log/LDF file give you an issue.

Also, in some cases, you can get into Enterprise Manager and detach/attach the DB. There are a few catches if you are going from SQL Server 7 to 2000 but usually those are easy to overcome.

Good Luck!

Thanks

J. Kusch

 

[JayKusch]

Oh and as a final wrap up. You could use the Export DB option in Enterprise Manager which uses the DTS data pump to recreate the DB at a target location, but I have found this method to be the least of my favorites because of all kinds of errors that this method is prone to.

Thanks

J. Kusch

 

[oblib]

Thanks for that J. I have "played" with both these options and will go back to the 'backup/restore' option. I'm sure when I tried this before there were some issues. Do the restore file and the target database have to be on the same drive letter as on the original machine? Or should I set up the physical files before attempting the restore?

 

[JayKusch]

You can direct where the file are restored to. Even though the backup was made, for example, the C:\MyDir folder, you can set the restore to place the file(s) in the E:\OtherDir folder on the target machine.

Thanks

J. Kusch