Quick Scan Report – Backup Without Checksum

What this check looks for

The most recent backup of each type per database, from msdb.dbo.backupset, where has_backup_checksums is 0. The check is skipped on Amazon RDS.

Why it matters

WITH CHECKSUM does two separate jobs, and most people only know about one of them.

First, it verifies pages on the way out. As the backup reads each page, it checks that page’s existing checksum. A page that is already corrupt in the database is detected at backup time, and the backup fails loudly rather than quietly copying the damage. That turns every backup into a partial integrity check, running on a schedule you already have.

Without it, corruption is read and written to the backup with no comment. The backup succeeds. The damage is now in your backup set, and it will be in every backup after it, and nobody knows until a restore or until DBCC CHECKDB next runs.

Second, it computes a checksum over the backup file itself. That is what makes this meaningful:

RESTORE VERIFYONLY FROM DISK = N'D:\Backups\YourDatabase.bak' WITH CHECKSUM;

Without backup checksums, RESTORE VERIFYONLY can confirm the file is a readable backup set and cannot tell you the contents are intact. With them, it verifies the whole thing. A backup that has been silently damaged on the storage or in transit is found before you need it, not during the restore.

The cost is small. Microsoft’s own guidance puts the CPU overhead at a low single digit percentage, which on a backup that is I/O bound anyway is usually not measurable. It is enabled by default in several other products for exactly that reason.

The one thing to be aware of: with checksums on, a backup of an already corrupt database fails. That is the feature working, and it can be a surprise the first time. If you need the backup regardless, CONTINUE_AFTER_ERROR takes it anyway and marks it as containing errors, which is the honest way to get a copy of a damaged database.

How to confirm it yourself

SELECT bs.[database_name],
       CASE bs.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential'
                      WHEN 'L' THEN 'Log' ELSE bs.[type] END AS [type],
       bs.[backup_finish_date],
       bs.[has_backup_checksums],
       bs.[is_damaged]
  FROM msdb.dbo.backupset AS bs WITH (NOLOCK)
 WHERE bs.[backup_finish_date] > DATEADD(DAY, -7, GETDATE())
 ORDER BY bs.[has_backup_checksums], bs.[database_name], bs.[backup_finish_date] DESC;

is_damaged = 1 on any row means a backup was taken with CONTINUE_AFTER_ERROR over known corruption, which is worth knowing about separately.

The instance level default, available from SQL Server 2014:

SELECT [name], [value], [value_in_use]
  FROM sys.configurations WITH (NOLOCK)
 WHERE [name] = 'backup checksum default';

How to fix it

Three ways, and the second is the one to use on any current version.

1. On the backup statement, which is explicit and works everywhere:

BACKUP DATABASE [YourDatabase]
  TO DISK = N'\\backupserver\sqlbackups\YourDatabase.bak'
  WITH CHECKSUM, COMPRESSION, INIT, STATS = 5;

2. As the instance default, SQL Server 2014 and later. This is the right answer, because it covers backups taken by anything, including ad hoc ones and tools you do not control:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'backup checksum default', 1;
RECONFIGURE;

It is dynamic and needs no restart.

3. Trace flag 3023, on versions before 2014, which achieves the same thing:

DBCC TRACEON (3023, -1);

Add -T3023 as a startup parameter to make it survive a restart.

Then update your backup jobs. Ola Hallengren’s DatabaseBackup takes @CheckSum = 'Y'. Maintenance plans have a “Perform checksum” option on the backup task. Third party tools usually have an equivalent setting and it is usually off by default.

And start verifying, because the checksums are only useful if something reads them:

RESTORE VERIFYONLY FROM DISK = N'\\backupserver\sqlbackups\YourDatabase.bak' WITH CHECKSUM;

A verify step after each backup is cheap and it is the thing that turns this setting into an actual assurance.

How long it takes

About an hour to set the default and update the jobs. No downtime.

Related reports

Related checks

Frequently asked questions

Does it slow backups down? A few percent of CPU, and backups are usually I/O bound, so it is rarely measurable. Compression has a far larger effect in both directions.

Our backup fails now that checksums are on. Then it found something. Run DBCC CHECKDB on that database. To get a copy of the damaged database anyway, use CONTINUE_AFTER_ERROR, and treat the result as a copy of a corrupt database rather than as a backup.

Is this a substitute for CHECKDB? No. It verifies pages the backup happens to read and does no logical consistency checking. It is a useful second net, not a replacement.

We use a third party tool. Look for the equivalent option in it. The instance default in SQL Server 2014 and later applies to native backups; a tool using its own VDI interface may need its own setting.