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];
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 🙂