How to migrate a database to a new instance

How to migrate a database to a new instance from SQL 2000 to newer version:

There are many ways to migrate a database to a different server (detach-attach, log shipping, etc) but this routine outlines the general considerations.

  1. Find out the best time to migrate the database
  2. Schedule the maintenance window and notify all users
  3. Script all logins, agent jobs and all other objects related to the database
  4. Save the information about the index usage statistics for the database
  5. Perform the move of the database (detach-attach, backup-restore, recover the tail of the log after the log shipping is complete etc.)
  6. Create the logins on the new server
  7. Map the orphaned database users
  8. Create all agent jobs and all other object related to the database

Not done yet…

How to complete the database migration:

  1. Make sure the Compatibility mode is set properly
  2. Make sure the Recovery mode of Page Verify setting is CHECKSUM

    declare @dbname sysname
    set @dbname = DB_NAME()


  5. Run the sp_updatestats

    declare @dbname sysname
    set @dbname = DB_NAME()
    EXEC (@dbname +'..sp_updatestats ''RESAMPLE''')


  7. Run the DBCC CHECKDB. Be careful what options you select for the CHECKDB, since it might take a very long time and a lot of resources (CPU, IO, tempdb space), depending on the size of the database. Check this article for more details.

If you need to decommission a database, then check this article.


2 comments to How to migrate a database to a new instance