The SQL Server team snuck in a new server configuration option in the 2014 release (I bet thanks to this Connect item even though it’s still Active), and it’s not documented so I just stumbled upon it recently.  If you run:

SELECT * FROM [sys].[configurations] ORDER BY [name];

you’ll see that there are 70 rows in the output (in 2012 there were 69) and the new one is:

backup checksum default

The option is disabled (set to 0) by default.  To enable it, simply run:

EXEC sp_configure ‘backup checksum default’, 1; GO RECONFIGURE WITH OVERRIDE; GO

As a quick reminder, adding the CHECKSUM syntax to the backup command forces SQL Server to verify any existing page checksums as it reads pages for the backup, and it calculates a checksum over the entire backup.  Remember that this does not replace CHECKDB (check out Paul’s post – A SQL Server DBA myth a day: (27/30) use BACKUP WITH CHECKSUM to replace DBCC CHECKDB – for more details).  So what does this server option do?  Well, since it’s not yet documented (I filed a Connect item here) I did some testing to confirm what I was expecting.

Within Management Studio I ran a simple backup statement:

BACKUP DATABASE [AdventureWorks2014] TO  DISK = N’C:\Backups\AdventureWorks2014_checksumtest.bak';

Then I checked the output in msdb:

SELECT [bs].[database_name], [bs].[backup_start_date], [bs].[backup_finish_date], [bs].[has_backup_checksums], [bs].[user_name], [bm].[physical_device_name] FROM [msdb]..[backupset] [bs] JOIN [msdb]..[backupmediafamily] [bm] on [bs].[media_set_id] = [bm].[media_set_id];

Backup information from msdb

Backup information from msdb


Check it out…with the server option enabled, I don’t have to include the CHECKSUM syntax to have SQL Server perform the backup checksum.

If you’re running SQL Server 2014, I highly recommend enabling this option for your instances, and if you rely on Glenn’s scripts for instance reviews, I know he’s adding a note about this to the next set :)