Database migration checklist

• Wilfred van Dijk

  SSCrazy Eights

  Points: 9003

  More actions

  June 9, 2008 at 3:01 am

  #122950

  Hi,

  I've a migration checklist I process during upgrading to 2005 and I think most of DBA's have such a list. Maybe it's an idea to come to a final list. Here are my steps:

  on old server:

  - dbcc checkdb on database to convert

  on new server (after restore of detach)

  - change compatibility level

  - change verification to checksum

  - dbcc checkdb() with DATA_PURITY

  - dbcc updateusage

  - sp_updatestats

  Wilfred
  The best things in life are the simple things

• Carolyn Richardson

  SSCrazy Eights

  Points: 8357

  More actions

  June 9, 2008 at 4:53 am

  #826024

  Depends on how you did the migration, you may need to check for orphan users...

  EXEC sp_change_users_login 'Report'

  Facts are stubborn things, but statistics are more pliable - Mark Twain
  Carolyn
  SQLServerSpecialists[/url]

• SC-564817

  SSC-Addicted

  Points: 408

  More actions

  June 9, 2008 at 12:21 pm

  #826258

  Hi

  I would like to add two things to your list

  1)orphaned users

  2)Did you take care of T-SQL changes.

  Wish you good luck..........

• phani kumar gotety

  Ten Centuries

  Points: 1213

  More actions

  June 9, 2008 at 5:35 pm

  #826400

  If you transfer the logins using the SP_HELP_REVLOGIN stored procedure, you would not get orphaned users as it recreates the logins with the original SID

  Link to the sp script: http://support.microsoft.com/kb/246133

• Vivien Xing

  SSChampion

  Points: 12297

  More actions

  June 9, 2008 at 10:15 pm

  #826430

  That is a good idea. Here is my part:

  http://technet.microsoft.com/en-us/library/ms143695.aspx (before/after the migration)

  and enable DAC.

• lduvall

  SSCarpal Tunnel

  Points: 4869

  More actions

  June 11, 2008 at 6:32 am

  #827195

  phani kumar gotety (6/9/2008)

  ---

  If you transfer the logins using the SP_HELP_REVLOGIN stored procedure, you would not get orphaned users as it recreates the logins with the original SID

  Link to the sp script: http://support.microsoft.com/kb/246133

  I used sp_help_revlogin and I still had a few orphaned users. I was going from a test server on a separate domain to a production server, though.

  To find the orphans, run

  use [database_name]

  go

  sp_change_users_login 'report'

  use [database_name]

  go

  sp_change_users_login 'update_one','[orphan_name]','[orphan_name]'

  Also, make sure you run the upgrade advisor before you proceed. I had some legacy applications that used old join syntax that had to be corrected. I have upgraded one production server, and it went really well. I have another one scheduled in two weeks. Good luck!