4

What's the ideal (or necessary) sequence of call when restoring a SQL Server 2012 backup onto a SQL Server 2014 instance and updating the compatibility level?

-- Update Statistics for all tables
EXEC sp_MSforEachtable 'Update Statistics ? with FULLSCAN';

-- Update page and row counts in catalogviews
DBCC UPDATEUSAGE('MY_DATABASE') ;

-- Change DB Compatibility level
EXEC sp_dbcmptlevel 'MY_DATABASE', '120';

I'm not sure if the db compatibility level effects the stats or simple the engine on the server. If the db compatibility level influences stats than it should be done before update stats. Also unclear if UPDATEUSAGE is still recommend and if it should be before update stats as well.

Improve this question

1 Answer 1

Reset to default
5

I would recommend to Update statistics as well as compatibility level to the latest version of sql server (provided your application supports it and you have done proper full business life cycle testing). I have written about full list of steps when moving from lower version to higher version

As a side note, please don't use sp_MSforEachtable - Its not supported. There is a clever way of doing it.

Compatibility level matters - see What is the actual behavior of compatibility level 80? and Clinging to old compatibility levels form Aaron Bertrand

Improve this answer
4
  • Do the sp_dbcmptlevel before update stats? And you don't recommend UpdateUsage? Good to be explicit about the lifecycle testing on the compatibility level.
    – Karl Kieninger
    Commented Aug 3, 2015 at 17:03
  • You should not use sp_dbcmptlevel, instead use ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 120. Also, after the database restore, it is not required to run DBCC UPDATEUSAGE unless you suspect incorrect values are being returned by sp_spaceused.
    – Kin Shah
    Commented Aug 3, 2015 at 17:28
  • At the risk of running to much into a tangent, why not the sp? The help text, at a glance, shows it basically a wrapper for the ALTER DB statement with some validation.
    – Karl Kieninger
    Commented Aug 3, 2015 at 17:43
  • 1
    sp_dbcmptlevel is deprecated - This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use ALTER DATABASE Compatibility Level instead.
    – Kin Shah
    Commented Aug 3, 2015 at 18:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.