2

Observations

The documentation says

To enable backup checksums in a BACKUP (Transact-SQL) statement, specify the WITH CHECKSUM option. To disable backup checksums, specify the WITH NO_CHECKSUM option. This is the default behavior, except for a compressed backup.

In other words, enabling compression enables checksums.

However, in my maintenance plans (unfortunately, they're the GUI ones and not Ola's cool ones), I can see:

  • "Backup Compression (On)" in the main menu
  • "BackupCompressionOption" set to "On" and "Checksum" set to "False" listed in the properties menu for the Backup Database Task.
  • 'Verify backup integrity' ticked in the Modify menu, 'Compress backup' selected, and 'Perform checksum' unticked.

The Question

Does this suggest that checksums are disabled even though I've explicitly asked for backup compression? Or are the tickboxes/properties for checksums redundant if compression is enabled?

What I've Tried

  • "View T-SQL" crashes SSMS every time that I try.
  • Finding the T-SQL in Query Store failed, even though I have it enabled on every database (I searched sys.query_store_query_text for like '%backup database%').
  • msdb.dbo.backupset has 0 in all of its has_backup_checksums columns, but I don't know if compression renders this column useless.
J. Mini
  • 1,161
  • 8
  • 32

1 Answers1

3

Here's what I think happens when you combine CHECKSUM and COMPRESSION:

COMPRESSION adds another level of checksum handling, independent of the backup command's checksum. IMO the documentation is vague in this area and should clarify how these things ties together.

I tried three different backup commands:

BACKUP DATABASE Adventureworks TO DISK = 'R:\aw.bak' WITH COMPRESSION, INIT
BACKUP DATABASE Adventureworks TO DISK = 'R:\aw.bak' WITH COMPRESSION, INIT, NO_CHECKSUM
BACKUP DATABASE Adventureworks TO DISK = 'R:\aw.bak' WITH COMPRESSION, INIT, CHECKSUM

I checked whether the backup header has checksum (RESTORE HEADERONLY). Only the one taken using the CHECKSUM option had 1 for that column.

I tried below command:

RESTORE VERIFYONLY FROM DISK = 'R:\aw.bak' WITH CHECKSUM

Above was OK for the backup taken with CHECKSUM, but for the other two, it failed with below error message:

Msg 3187, Level 16, State 1, Line 9
RESTORE WITH CHECKSUM cannot be specified because the backup set does not contain checksum information.
Msg 3013, Level 16, State 1, Line 9
VERIFY DATABASE is terminating abnormally.

Above indicates to me that the only way to get the backup command's checksum handling is by specifying the CHECKSUM option (or default it using sp_configure).

Furthermore, I hacked the file, changing some random data with a hex-editor. For all three backup commands, I got below error:

Msg 3203, Level 16, State 1, Line 9
Read on "R:\aw.bak" failed: 13(The data is invalid.)
Msg 3013, Level 16, State 1, Line 9
VERIFY DATABASE is terminating abnormally.

This indicates to me that the compression's checksum handling is, at restore time, done prior to the backup command's checksum verification.

Finally I tried the same (hack with a hex-editor) for a non-compressed backup, and that gave be below error for VERIFYONLY:

Msg 3189, Level 16, State 1, Line 9
Damage to the backup set was detected.
Msg 3013, Level 16, State 1, Line 9
VERIFY DATABASE is terminating abnormally.

I.e., a different error message. This is IMO the error message that the backup commands checksum verification throws when it fails the verification. In contrast to the error from the compression codes verification (as shown earlier).

For the record, when I modified the file. I modified it at three different places for all tests. I changed the value for a byte, saved the file, did a verify, and then changed it back to original and verified that the backup was now clean. At three different places in the files. The error messages were consistent for all tests, so it is very unlikely that I for all 9 changes I did would hit some meta-data area in the backup file.

Tibor Karaszi
  • 18,269
  • 2
  • 17
  • 30