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 🙂
6 thoughts on “Backup checksum default option in SQL Server 2014”
Yet another really useful feature for this edition. I am glad to see that this no longer needs to be manually done. Thanks for letting us know!
It’s worth to mention that in previous versions of SQL Server this can be done with trace flag 3023.
On my opinion it’s more convenient to use sp_configure than trace flags. Thanks to SQL Server 2014 developers!
Thanks Erin for providing this hidden info! This is an awesome addition. I am glad I stumbled upon your blog post.
BTW, I see “Backup Checksum Default” option in my sys.configurations, but I get only 68 rows! We are running on 2014 RTM. Out of curiosity, I checked on one of our 2012 Instances, and it has got 67 rows.
Looks like it was documented (KB Last Review: 04/09/2014 19:12:00): https://support.microsoft.com/en-us/kb/2656988
Thanks Alex, I see it’s also in the 2016 documentation for Server Configuration Options (https://msdn.microsoft.com/en-us/library/ms189631%28v=sql.130%29.aspx) but not in the 2014 documentation.