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’
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.