Database backup checksum option will you use it?

SQL Server has a lot of features and options, and many feature options are not commonly used. But if there is such a need, then I can't remember it.

Let's talk about the options in the backup. Checksum, what is this thing? You've never heard of it? View

　　　

This is the option! I wonder if you crossing know what it is? How to use it? I asked a few groups of friends are useless, just have the environment, then I will use examples to show you!

--------------Blog Address---------------------------------------------------------------------------------------

Expert Diagnostic Optimization Series http://www.cnblogs.com/double-K/

No more nonsense, just open the whole-----------------------------------------------------------------------------------------.

First, let's take a look at Microsoft's official documentation for instructions:

Error management options

Use these options to determine whether backup checksums are enabled for the backup operation and whether the backup operation will stop if an error is encountered.

{No_checksum | CHECKSUM}
Controls whether the backup checksum is enabled.

No_checksum
Explicitly disables the generation of backup checksums (and validation of page checksums). This is the default behavior.

CHECKSUM
If this option is enabled and available, specifying a backup operation verifies the checksum and page deformity for each page and generates a checksum for the entire backup.

Using backup checksums can affect workload and backup throughput.

This may be the understanding of most people " enabled backup checksum, the backup operation will stop when encountering an error ", because read the official documents know how to use, there is a bad library at hand, just do a demonstration ~

Let's take a look at the results of the DBCC CHECKDB, just to tell you that this is a database with bad pages!

　　

Well, let's do a backup look!

Normal backup, no checksum successful backup!

　　

　　

Plus checksum backup, failed!

　　

　　It seems to be the same as the Microsoft documentation says! See this some crossing will think, this and I do checkdb almost?

　　

Bo Master Other not much is bad library more! Let's get another library and try it!

Let's take a look at the results of the DBCC CHECKDB, just to tell you that this is a database with bad pages!

　　

Let's back up with checksum.

　　

Oh?? It worked??? What does that mean??

Let's first look at the results of Backupset:

　　　

so the damaged page is not check out? Let's see suspect_pages again .

　　　

Suspect_pages only found the test library of the bad page, then I xb28_3 the library of the bad page where?

Suddenly think of suspect_pages inside the data is only 823 error or 824 error and failed page!

　　　　and using the checksum system table will be omitted, this article Xb28_3 library's bad page is a object_id = 60 of the system table!

and not all the pages in the database are called on the page checksum, and when the page corruption occurs, the IO system does not differentiate whether the damaged page has a page checksum or no checksum. So use Backup ... With checksum it is possible to cause some corrupted pages to not be discovered .

　　

　　　　This also means that the use of checksum in backup does not completely replace the role of DBCC CHECKDB, which is consistent with Paul Randal's article on the Great God: A SQL Server DBA myth a Day: (27/30) use BACKUP with CHECKSUM to replace DBCC CHECKDB

--------------Blog Address---------------------------------------------------------------------------------------

Expert Diagnostic Optimization Series http://www.cnblogs.com/double-K/

-----------------------------------------------------------------------------------------------------

　　

Summary: The checksum option for backup can be used to discover whether a database is corrupt, but not all types of bad pages can be found.

Crossing's question: backup with checksum can be used to replace DBCC CheckDB.

This view is not correct, some logical consistency error checksum is impossible to find!

Database corruption is not very common, many DBAs have not met for many years, but it is necessary to do a good job of CHECKDB inspection! In case you don't find it, it's more tragic!

　　PS: Differential backups can also use checksum, but only the difference part is verified ~

----------------------------------------------------------------------------------------------------

Note: This article is original, welcome reprint, please in the article page obvious location give this article link!
If you think this article is not bad please click on the lower right corner of the recommendation , thank you very much!

Database backup checksum option will you use it?