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

Source: Internet
Author: User
Tags time interval backup

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/

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.