SQL Server 2014: Checksum with backups

At the MTC, I have been a big time supporter of using CHECKSUM’s in your Page_Verification setting as a best practices. Some of these technology innovations that we do are hidden deep somewhere in-between the options that we don’t get to see. Inorder to get the best of your SQL Server and detect IO related potential issues, it is highly recommended to turn this setting ON.

Now, we also recommend using the CHECKSUM verification with backups. Many a times have seen customers actually have their own third party solutions to take backups and many times this option is never being used. The consequence of this is, a bad backup yields a bad corrupt restore too. So I highly recommend you read this KB article for prior to SQL 2014 deployments.

What is new with SQL 2014?

From SQL Server 2014 onwards we introduce a capability of enabling checksum at the server level by default irrespective of the backup tool that you use. This is an nifty and handy setting and I will be recommending my customers to use this by default.

The image above shows the setting available by default. So to enable the same use the following command.

sp_configure ‘backup checksum default’, 1



As I sign off this new learning, wanted to check how many of you use the CHECKSUM feature as described at the database level atleast? I am getting into this small learning experiences working with SQL Server 2014 for a while and I will keep posting them in future blogs too.

Share this article

Leave a Reply

  1. This is with regard to the checksum property setting. We have it enabled in the Page_Verification setting. As far as the backups are concerned in the GDC we use Veritas NetBackup SQL Agent and have the backup job scheduled in the Control M environment. This is the standard procedure as we have large databases to be backed up as per our backup policies. This article has helped us to do a check with the DBA if the checksum has been enabled as part of the backup policies configured in the NetBackup SQL Agent.
    Please can you let us know of any best practices that we need to follow in this regard and if there any standard data center tools that will help us with backup and recovery process. Your guidance is very much valued in this regad.

    • There is no tools as such to look out for, as described in the article KB Article (http://support.microsoft.com/kb/2656988) is a great starting point to make sure those are enabled when using third party applications for backup. It is a bunch of trace flags and process that needs to be implemented in your organization. Thanks for dropping a line and enquiring about the same.