Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Hey guys, I'm tasked with doing a SQL Server 2000 to 2005 migration. I will be doing a side-by-side migration. I've read from a few different sources that I can either:

  • perform a backup of the database on SQL Server 2000, ship that backup to the new hardware and restore the database in SQL Server 2005; or

  • detach the database on SQL Server 2000, copy the data and log files to the new hardware and attach the files in SQL Server 2005.

Is there a difference between these methods? Which is the safest?

I've been through the pre-upgrade tasks of checking the database with Upgrade Advisior and I have no warnings to resolve before the migration.

Any comments would be greatly received.

Thanks

Joe

share|improve this question
add comment

migrated from stackoverflow.com Jan 21 '12 at 12:35

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 4 down vote accepted

For speed (eg less downtime) backup/restore. Backup/restore is quicker because of Instant File Initialisation (only for MDF). The actual backup file is a lot smaller then the MDF/LDF sizes so copies quicker.

It's arguably slighty less risky because there is no downtime on the SQL 2000 instance.

However, detach removes access to the old database. You'll have to take this offline with a backup/restore. If there is a risk of end users connecting, use this (or take offline as soon as backup is complete)

Basically, it's whatever suits you best. I've used both methods for a recent server migration.

share|improve this answer
    
It's possible that you don't want people accessing the database (for insert at least) once you've started the backup -- you'll be losing their changes when you restore it somewhere else. My point is this: sometimes downtime's unavoidable. –  Roger Lipscombe Sep 16 '09 at 14:24
    
As long as you don't delete, modify, or otherwise damage the SQL 2000 "source" files, either method should be perfectly "safe". –  Philip Kelley Sep 16 '09 at 14:26
    
Now, "accurate" or "completely up-to-date", that's a different subject, as has been pointed out. –  Philip Kelley Sep 16 '09 at 14:27
    
@Roger: Absolutely. I tried to cover most bases –  gbn Sep 16 '09 at 14:27
    
@Philip: yes, I did say "arguably". –  gbn Sep 16 '09 at 14:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.