Copy Database vs Backup/Restore Database
-
Hi All,
Whenever required to take Backup of Database . I used to do Backup and Restore option. I never use Copy Db to migrating DB from one server to other Server.
can anyone please help me to find the difference between Copy Database Wizard and Backup/Restore option. and which one is better to use for production Db.
Thanks,
Arooj Ahmad
-
Backup/restore - backup the database to a file. Restore from file.
Copy database wizard - run object creation scripts on destination and copy data one table at a time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks Gail for immediate response 🙂
-
For a production database, your best bet to protect the database is backups.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
I've personally never had much luck with the copy wizard. Backup/restore is always going to be more reliable, faster, and more repeatable. It also keeps that extra I/O away from your source box cause it doesn't have to read all those tables in realtime.
You can tune your backups as well so you can really speed it up as your DBs get bigger.
I've got a video that shows you how to tune your backups if you're interested.
http://midnightdba.itbookworm.com/EventVids/SQLSAT90BackupTuning/SQLSAT90BackupTuning.wmv
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply