quote:

---

Since most of these operations are one-time things generally specific to your
environment, you're probably better off doing most of this by hand to ensure
it's done correctly. For this process, I would suggest the following course
of action:

1) Create Full Backups of the databases you want to transfer from the SQL
2000 environment
2) Take those databases off line to ensure no additional data is added to
them
3) Restore the SQL 2000 backups to the designated SQL 2005 database
4) Change migrated DBs to the Compatibility Level 90 (Right-Click on the DB
-> Properties -> Options)
5) Change migrated DB Page Verification models to CHECKSUM (Right-Click on
the DB -> Properties -> Options)
6) Rebuild all table indexes for all migrated DBs (DBCC REINDEX)
7) Update all table statistics for all migrated DBs (sp_updatestats)
8) Create login accounts/groups that need to access to the migrated DBs
9) Run sp_change_users_login 'REPORT' on each migrated DB and then fix the
orphaned logins by running sp_change_users_login 'AUTO_FIX', 'userName'
10) Run app/user/db tests to ensure functionality isn't affected by the new
SQL environment
11) Drop offline databases from SQL 2000 instance

This doesn't take into account any supporting DTS packages or jobs that you
would need to migrate as well. I would also recommend running the SQL 2005
Upgrade Advisor (Start -> All Programs -> MS SQL Server 2005) before you do
any of this as well as it may point out some things that you should change or
address during the migration. With this plan, if you need to roll back
anything, you can just bring the SQL 2000 DBs back online and drop all the
migrated DBs, address the encountered problems, and try again.

John E,SQL DBA

---