Misunderstanding #27: Using BACKUP... with checksum can replace DBCC CheckDB
Error
At first glance, because backup with checksum will detect the CHECKSUM values of all allocated pages, this misunderstanding seems to be the case, but in fact it is not the case, for the following reasons:
The page checksums feature of the database upgraded from SQL Server 2000 or earlier must be enabled. When enabled, not all pages in the database are called for the previous page checksum. When the page is damaged, the IO system does not distinguish whether the corrupted page has a page checksum or no checksum. Therefore, the use of BACKUP... with checksum may cause some damaged pages not to be found, and the consequences ......
In addition, there is also a problem that the time interval for full backup is relatively long. For example, one month, and the best practice for DBCC CheckDB is one week, which leads to the failure of with checksum to replace CHECKDB. Even if you perform differential backup every week, the differential backup only checks the page checksum of the differential part.
The last and most harmful aspect is that the backup with checksum option cannot be used to detect page corruption in the memory. This is because pages in the memory are damaged due to memory chips or WINDOWS processes and are written back to the disk. This results in a normal checksum for damaged pages. Only DBCC CheckDB can be used to detect such errors.
Therefore, in the end, you must always use dbcc checkdb. If you still have doubts about this, read my previous article: CHECKDB From Every Angle: Consistency Checking Options for a VLDB.
Extended reading: Search Engine Q & A #26: Myths around causing upload uption
