Sample Header Ad - 728x90

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