SQL Server myth: using Backup ... With checksum can replace DBCC CheckDB

Myth #27: Use Backup ... With checksum can replace DBCC CheckDB

Error

At first glance, because backup with checksum detects the value of the checksum for all assigned pages, this myth seems to be the case, but it's not really the case, for the following reasons:

The database page checksums that was raised by SQL Server 2000 or earlier must be turned on. After it is turned on, not all pages in the database will be called on the page checksum, and when the page corruption occurs, the IO system does not distinguish between the page checksum and the checksum of the corrupted page. So use Backup ... With checksum can cause some damage to the page not to be found, the consequences ...

In addition, there is a problem with the time interval of a full backup is relatively long, if one months, the best practice relative to DBCC CHECKDB is a week, which causes the with checksum not to replace CHECKDB. Even if you make a differential backup every week, the differential backup detects only the page checksum for the difference section.

Finally, the most damaging point is that using the backup with CHECKSUM option does not reveal corrupted pages in memory. This is because memory chips or Windows processes cause pages in memory to be corrupted and write back to disk after this. This causes the corrupted page to have a normal checksum, and only use DBCC CHECKDB to discover such errors.

So, in the final analysis, you have to use DBCC CHECKDB frequently, and if you still have questions, see my previous article: CHECKDB from Every angle:consistency Checking Options for a VLDB.

Extended reading: Search Engine q&a #26: Myths around causing corruption

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/