Does enabling backup compression in a Back Up Database Task enable checksums, no matter what setting you pick for them?
2
votes
1
answer
213
views
## Observations
[The documentation says](https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/enable-or-disable-backup-checksums-during-backup-or-restore-sql-server?view=sql-server-ver16#enable-or-disable-backup-checksum-for-a-backup-operation)
> 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.
Asked by J. Mini
(1235 rep)
Jul 20, 2024, 01:01 PM
Last activity: Jul 22, 2024, 06:38 PM
Last activity: Jul 22, 2024, 06:38 PM