Ravi Khanal

Database Migration is always a very challenging job since data is the major component of any System and it should always be secured. You are not migrating database when any new version of the database is in the market. You have to spend a lot time researching whether the database migration is really fruitful. After the data migration from one version to another, you should have the all the usefullness of the previos version with the usefullness of the new one.

Some of the best practice that I used to follow while migrating database from SQL Server 2000 to SQL Server 2005 are:

1. There are two types of Migration, In Place migration and Side by Side migration. In Place migration is migrating new database on the same system. Side by Side Migration is migrating the database in completely new system. In Place migration is always a dangerous choice. You cannot get back to the previous condition after migration. So try to avoid In place migration.
2. Use the Side by Side Migration.
3. Run the upgrade advisor tools on the SQL Server 2000 machine.
4. Address all the compatibility issues and re-run the upgrade advisor tool to ensure zero compatibility issue.
5. When the new hardware is ready and the downtime is decided, detach the database from the existing database instance.
6. Move the detached database file and log file to the destination machine.
7. Attach the mdf and ldf files to the new SQL Server 2005 by using the create database statement FOR ATTACH or FOR ATTACH_REBUILD_LOG.
8. Once the database is attached, check your database whether everything is running perfectly fine as of before.
9. Change the Compatibility mode from 80 to 90.
10. Run some DBCC commands like DBCC checkdb, DBCC checkalloc, DBCC Updateusage(dbname)
11. Transfers logins.
12. Transfer all old DTS packages using SSIS DTS import tools.
13. Transfer all the jobs, maintenance plan using SSIS tasks.