A SQL Server DBA myth a day: (27/30) use BACKUP WITH CHECKSUM to replace DBCC CHECKDB

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.)

A short one today as I’m up to my eyeballs with client work and sorting through SQL Connections abstract submissions!

Myth #27: you can avoid having to run DBCC CHECKDB (or equivalent) by using BACKUP … WITH CHECKSUM

FALSE

On first glance, this seems like it should be true as the WITH CHECKSUM option will cause all existing page checksums on allocated pages to be checked. But it’s not and here’s why:

Firstly, on a database that’s been upgraded from SQL Server 2000 or earlier, page checksums must be enabled. After they’re enabled, not all pages in the database will have page checksums on them – and an I/O subsystem does not distinguish between pages with and without page checksums when causing corruption. So if all you do is use BACKUP … WITH CHECKSUM, there may be corruption that you won’t find until it’s too late… (I’ll just leave the ‘until it’s too late’ hanging there… you can imagine scary consequences for yourselves :-)

Secondly, you might only take a full database backup once per month, which isn’t a frequent enough consistency check for my liking (I recommend at least once per week). Even with a weekly differential backup, that’s not going to check all allocated pages in the database – only those in extents that it backs up (those extents that changed since the last full backup).

Lastly, and most insidiously, relying solely on BACKUP … WITH CHECKSUM leaves you susceptible to in-memory corruptions. If a bad memory chip, badly-written XP, or other rogue Windows process corrupts a SQL Server data file page in memory, and then it gets written to disk, you’ve got a corrupt page with a valid checksum – and nothing will catch that except DBCC CHECKDB.

Bottom line – you can’t avoid running consistency checks. If you’re having trouble, take a look at my old blog post CHECKDB From Every Angle: Consistency Checking Options for a VLDB.

And while we’re on the subject, check out this post: Search Engine Q&A #26: Myths around causing corruption.

8 thoughts on “A SQL Server DBA myth a day: (27/30) use BACKUP WITH CHECKSUM to replace DBCC CHECKDB

  1. Nope – because that doesn’t put a page checksum on all pages in the database – you’ll still be missing system tables.

  2. So there is no way to fully upgrade a database to have page checksums, short of building a new DB and moving everything? I would have thought that a restore would calculate and save the checksum as it restores. (Maybe in a future version.)

  3. Only way is to rebuild all your indexes, or force in-place updates of all table and index rows – but that still doesn’t touch system tables etc.

  4. Nope – that’s the only bummer about page checksums. And no, backup/restore cannot change the data… see the restore myths post.

  5. What about turning TDE on waiting for the DB to be encrypted, then turning TDE back off? That would do it wouldn’t it? It is sort of using an anvil to push in a push-pin, but it would get the job done right? Of course there are lots of issues to deal with, major IO/CPU load, backing up the keys, etc. (And it isn’t an option with SQL Server 2005.)

  6. I believe so – but definitely the sledgehammer approach (as it encrypts tempdb too). I’ll have to check to make sure.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.