May 10, 2004 at 7:25 am
Hello again. Long time since I've been in here.
Are there any way I can copy my database without taking it offline? I was thinking of a save as solution, but I can't find it in SQL bol. I might be wrong.
I was thinking of sp_detach_db SP. And rename the file on the server. And use sp_attach_db and attach the old and the new databases. But then the databsase is taken offline. How can I avoid that?
-Lars
Please only reply to this newsgroup. All mails would be bounced back.
May 10, 2004 at 8:41 am
Just backup and restore to a different database name. eg :
restore database new_db from disk = 'c:\old_db.bak' with replace,
move 'file1' to 'c:\new_file1.mdf',
move 'log1' to 'c:\new_log1.ldf'
You'll need a MOVE statement for each logical file in your database (or just the 2 as above if you only have 1 data file and 1 log file).
You don't need to create the new database in advance, though that's also fine.
Check in BOL for further options
Regards
Rob
May 11, 2004 at 9:02 am
You can also use Data Transformation Services (DTS Import/Export Wizard) to copy the objects and the data of an SQL Server database to another SQL Server database.
Regards
May 11, 2004 at 9:49 am
DTS has been flaky to me. I prefer backup/restore, no downtime, works, etc. detach/attach works, but requires downtime.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply