Written by:R Teachout
7/3/2008 1:43 AM

These are my recommended steps to migrate a database from SQL 2000 to SQL 2005.

1. Backup the database on SQL 2000
2. Take database offline on the SQL2000 to make sure no additional data is written to it (see my other article on setting a database to single user mode)
3. Restore the DB on SQL 2005
4. Change the compatibility level to 90 (Properties of the DB->Options)
5. Change DB Page Verificaton Models to CHECKSUM (Properties of the DB->Options)
6. Rebuild indexes and statistics
   use [dbname]
   DBCC reindex
   sp_updatestatus
7. Fix orphaned users / add new users
   To find them, run the following sp_change_users_login 'REPORT'
8. Change application to connect to the 2005.  V
9. Verify, if all is well, drop the SQL 2000 database

Voila!

(Don't forget, you may also need to transfer DTS Packages or SQL Jobs related to this database also!)

TrackbackPrint

Location: BlogsRamblings, Scriptlets, and more!MSSQL Scriptlets

Your name:
Title:
Comment:
Security Code Enter the code shown above in the box below
Add Comment   Cancel