Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLCode
Posting Yak Master
143 Posts |
 Posted - 2004-03-31 : 13:30:15
|
| Hi guys,I have a quick question.I have to restore/create replica of few databases really quickfrom ServA to ServB.I was wondering which is faster option.1. If I restore(on ServB) from backup files on Serv A.2. Using DTS to copy/transform data and objects.Any advice is greatly appreciated. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-31 : 13:32:07
|
| BACKUP/RESTORE option will be faster than DTS. sp_detach_db/sp_attach_db will be faster than BACKUP/RESTORE.Tara |
 |
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2004-03-31 : 13:38:32
|
| Thanks Tara. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-03-31 : 18:17:25
|
| If you're going to do this though download robocopy.exe off of download.com. You can write a procedure to detach/copy/attach. Robocopy is much faster than a standard copy. You can then just schedule the job with SQLAgent to run the procedure.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-04-01 : 02:05:44
|
| >> BACKUP/RESTORE option will be faster than DTS. sp_detach_db/sp_attach_db will be faster than BACKUP/RESTORE.Not necessarily.The copy of the files may take a long time in which case a backup/restore would be faster (large tr log file or lot of wasted space).Sometimes using dmo to copy objects is faster than any of these options.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-04-01 : 08:58:00
|
| If you are going to do the backup though, you should use robocopy to still copy the files. That or ftp will be much faster than standard copy.How would dmo be faster? Can you explain how you would set that up?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-04-01 : 09:03:21
|
| Depends on what you have to transfer and what is in the database.If you have a lot of tables that you don't need or fragmentation (I think) then dmo can just transfer the objects you need.One installation where we had to send through a firewall reduce 3 hrs for the backup restore to around 45 mins.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
SQLCode
Posting Yak Master
143 Posts |
Posted - 2004-04-01 : 11:28:04
|
| I did not know about the Robocopy. Thanks for the insight. The databases to be copied are the databases entirely from staging to production and not selected objects in the databases.Thanks for all the good info. |
|
|
|
|
|
|
Copy database with DTS or from backup files