I am not sure if this is a bug or an issue with BOL documentation, but BOL says here http://msdn.microsoft.com/en-us/library/ms186865.aspx: “(CHECKSUM) Specifies that the backup operation will verify each page for checksum and torn page, if enabled and available, and generate a checksum for the entire backup. This is the default behavior for a compressed backup”.

So, let’s run some tests.

Create three backups: backup a database without compression, then backup a database with compression and finally backup it with CHECKSUM:

BACKUP DATABASE msdb TO DISK = ‘K:\BACKUPS\msdb_compressed.bak’

WITH COMPRESSION, COPY_ONLY

BACKUP DATABASE msdb TO DISK = ‘K:\BACKUPS\msdb_NOT_compressed.bak’

WITH COPY_ONLY

BACKUP DATABASE msdb TO DISK = ‘K:\BACKUPS\msdb_CHECKSUM.bak’

WITH CHECKSUM, COPY_ONLY

Run RESTORE HEADERONLY for the backups created without CHECKSUM:

RESTORE HEADERONLY FROM DISK = ‘K:\BACKUPS\msdb_compressed.bak’

RESTORE HEADERONLY FROM DISK = ‘K:\BACKUPS\msdb_NOT_compressed.bak’

Actual results: Both results have “HasBackupChecksums” column equal to 0.

Expected Results: Expecting “HasBackupChecksums” column to have value equal to 1 for the compressed backup (based on documentation).

Run RESTORE HEADERONLY for the backup created with CHECKSUM:

RESTORE HEADERONLY FROM DISK = ‘K:\BACKUPS\msdb_CHECKSUM.bak’

“HasBackupChecksums” column is equal to 1 as expected.

It does not look that CHECKSUM enabled automatically when COMPRESSION is enabled for the backup.

So, the questions are:

• Is this the documentation issue?
• Does compression really automatically enables checksum for the backups?

I have sent these questions to Microsoft, so if you want to know the answers as well – come back later. I will publish thier response as soon as I get it.