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 |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2005-02-22 : 10:05:15
|
| My dear dba gurus,Here is a situation that I need your help again.I need to copy a production sql2k db to a separate testing server on the network. My scheduled DTS last night got hang up, and when I got here they already killed it because it locked down the production server. The size of the db is 19G. I have a full back file as well. The test server has a new instance sql without any schema about the db. Both of the servers use SAN storage.What is the best way to copy the database? By best, I mean least time and absolute no interruption of the production server.Thanks! |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-02-22 : 11:08:32
|
| Best is to copy a backup.These will copy the latest backup and restore ithttp://www.mindsdoor.net/SQLAdmin/s_CopyLatestBackup.htmlhttp://www.mindsdoor.net/SQLAdmin/s_RestoreLatestBackup.htmlthis will do it in one processhttp://www.mindsdoor.net/SQLAdmin/s_TestRestore.htmlBut it means if the restore fails (e.g. due to a user being in the database then you have to do the copy again - the seperate tasks are better.Call them from a job so you can run them easily.==========================================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. |
 |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2005-02-22 : 11:26:12
|
| Wow, thanks. I'll definitely try them out. |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2005-02-22 : 11:52:48
|
| nr,I got this message when I run first copy sp.Server: Msg 50000, Level 16, State 1, Procedure s_CopyLatestBackup, Line 53Any idea why? I think that line has this: line 52 select @cmd = 'file ' + @filename + ' already transferred'line 53 raiserror(@cmd, 16, -1) |
|
|
|
|
|
|
Better way to copy database