I reviewed the SQL Server 2012 technical upgrade guide and also googled for more recommendations.

I am migrating a SQL Server 2008 R2 instance to SQL Server 2012 on a separate machine.

Here is what I plan to do. Please correct and advise your recommendations.

  • On SQL Server 2008 R2 instance:

    • I ran the SQL Server upgrade advisor instance.
    • Take backup after all activity is stopped.
  • On SQL Server 2012 instance:

    • Restored SQL Server 2008 R2 backups
    • Update the database compatibility level to 110
    • dbcc checkdb with data_purity
    • EXEC sp_updatestats;
    • EXEC sp_MSforeachtable @command1='UPDATE STATISTICS ? WITH FULLSCAN';
    • DBCC UPDATEUSAGE(dbname);

I will have the logins, job setup separately.

Do I need to rebuild the indexes? Any thing else?

I truly value your input and hope to hear your recommendations.

Thank you.

  • I read through some more material but would like clarification on the following - Do I need to rebuild index post migration or just update stats or both? Is this correct? EXEC sp_MSforeachtable @command1='ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)'; GO EXEC sp_MSforeachtable @command1='UPDATE STATISTICS ? WITH FULLSCAN'; GO Feb 20, 2014 at 21:03
  • 2
    Update stats is highly recommended. Rebuilding all indexes without looking into their fragmentation levels, is waste of resources - but if you have window to do it, then go for it. Also, sp_MSforeachtable is undocumented. Better use Ola's maintenance solution that my answer refers to. HTH !
    – Kin Shah
    Feb 20, 2014 at 21:29