DBCC CHECKDB and BACKUP with CHECKSUM

I came across a few forums and there are several questions regarding the BACKUP with CHECKSUM and DBCC CHECKDB. People wonder if the BACKUP with CHECKSUM can replace DBCC CHECKDB. Let's find out more about what these commands actually do.

DBCC CHECKDB perform integrity check on objects in the database. It checks the consistency of the disk space allocation structures, pages and structures of table and view, catalog consistency, and other database and link level consistency validation. This command is highly recommended to be performed regularly to detect corruption as early as possible.

What is a checksum? It is digital sum of correct digits or hash over a list of arguments, and often used for equality comparison.

Before we discuss about BACKUP with CHECKSUM option, let's look into PAGE_VERIFY database option. From SQL Server 2005, Database PAGE_VERIFY has 3 options, CHECKSUM and TORN_PAGE_DETECTION or NONE. More information between the difference can be found at MSDN. CHECKSUM is the default and recommended option. This option compare the page checksum value stored in the header when the page is read from disk (more on this later). It reports error when the values do not match (corruption).

BACKUP with CHECKSUM option verify each page with its checksum during the backup operation, and generate a checksum for the backup. The backup operation reports error when page corruption is found. This backup checksum can be used to validate the backup during RESTORE with CHECKSUM option operation (including RESTORE VERIFYONLY). 

One common question many people ask, can BACKUP with CHECKSUM option replace DBCC CHECKSUM. Short answer is no. Although BACKUP with CHECKSUM check the page checksum during the backup operation, it doesn't address the possibility of data file page corruption in memory before written to the disk. The page is corrupted however with a valid checksum. This behavior is explained here by Paul Randal blog post. This question was raised usually because when system is running out windows for maintenance and admin try to find alternatives to perform consistency check. 

One way to mitigate this issue is perform a full backup, restore the backup on a separate server and perform DBCC CHECKDB there. This method actually kill two birds with one stone. First it verify the backup, and secondly it offloads the resource intensive consistency check workload from the production to other server. Another way is to split out consistency check as suggested by Paul Randal (Perform DBCC CHECKALLOC, DBCC CATALOG, DBCC CHECKTABLE separately) or use DBCC CHECKDB with PHYSICAL_ONLY option checking only the physical consistency of the database on between the regular DBCC CHECKDB maintenance schedule. This way, at least the physical consistency is validated frequently.