I have many backups on my SQL Server 2008 R2 system, and I must restore them on new SQL Server 2012 SP2.

My question is, after the restore on new SQL Server 2012, what can I do to verify that everything is ok?

I'm sure that it is necessary to change the compatibility level, but is it necessary to run a DBCC CHECKDB and an UPDATEUSAGE command?

Is there a need to perform other particular checks? What's the best way?

Cheers

As you noted, you'll be running the DBCC CHECKDB command (note that DBCC just means "Database Console Command", whereas CHECKDB is the command it is executing).

This will be sufficient to ensure that the databases have survived the restore process.

For complete peace of mind, you can issue this command on each database:

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS;
  • EXTENDED_LOGICAL_CHECKS will do extended checks on "indexed views, XML indexes, and spatial indexes, where present" (per the documentation);
  • NO_INFOMSGS returns no results if no errors were detected.

Incidentally, updating the compatibility isn't a required step. The databases can stay at the 100 compatibility level until you're ready to take advantage of the newer features of 2012 that require a newer compatibility level.

If you perform your backups with the CHECKSUM option, you can do an additional verification check during the database restore for added peace of mind.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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