(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
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.