Using SP_CONFIGURE Enable or Disable BACKUP CHECKSUM DEFAULT Configuration Option in SQL Server 2014

Using SP_CONFIGURE Enable or Disable BACKUP CHECKSUM DEFAULT Configuration Option in SQL Server 2014 / How to Configure BACKUP CHECKSUM DEFAULT in SQL Server 2014

Read Comments

Introduction

This article explains the steps to enable or disable BACKUP CHECKSUM default configuration option at instance level in SQL Server 2014 using SP_CONFIGURE system stored procedure. This is a very useful configuration setting especially when you are using SQL Server Maintenance Plans or Third Party Tools to Perform Full, Differential or Transactional Log backups in SQL Server 2014. Once you enable this setting all database backups and restores on the instance will use CHECKSUM option by default.

It is important to set PAGE_VERIFY Database Option to CHECKSUM for all databases in SQL Server. When CHECKSUM is used as PAGE_VERIFY option for a database it helps in detecting database consistency problems with the system I/O path and provides a high level of data-file integrity. For more information, see Setting PAGE_VERIFY Database Option to CHECKSUM for all Databases in SQL Server.

If you would like to Use CHECKSUM option for all backups and restores operations on SQL Server Instances Prior to SQL Server 2014 then see, Enable BACKUP CHECKSUM in SQL Server Using Trace Flag.

How to Enable BACKUP CHECKSUM Option at SQL Server Instance Level using SP_CONFIGURE

A DBA can enable BACKUP CHECKSUM default configuration option using SP_CONFIGURE system stored procedure. Execute the below script to enable BACKUP CHECKSUM option at instance level in SQL Server 2014.

Use master
GO

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure 'backup checksum default', 1
RECONFIGURE WITH OVERRIDE
GO

Query Output

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'backup checksum default' changed from 0 to 1. Run the RECONFIGURE statement to install.

Once 'backup checksum default' is configured at instance level in SQL Server 2014 then there is no need to specify BACKUP DATABASE … WITH CHECKSUM or RESTORE DATABASE … WITH CHECKSUM explicitly within the TSQL code.

However, if you would wish to override this setting (not recommended though) then you can specify NO_CHECKSUM clause for a specific BACKUP or a RESTORE command i.e. BACKUP DATABASE … WITH NO_CHECKSUM or RESTORE DATABASE … WITH NO_CHECKSUM.

How to Disable BACKUP CHECKSUM Option at SQL Server Instance Level using SP_CONFIGURE

A DBA can disable BACKUP CHECKSUM default configuration option using SP_CONFIGURE system stored procedure. Execute the below script to disable BACKUP CHECKSUM at instance level in SQL Server 2014.

Use master
GO

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXEC master.dbo.sp_configure 'backup checksum default', 0
RECONFIGURE WITH OVERRIDE
GO

Once you have disabled CHECKSUM option at instance level, then you will have to specify CHECKSUM option within your TSQL script to perform backups or restores to leverage CHECKSUM option. i.e., BACKUP DATABASE … WITH CHECKSUM or RESTORE DATABASE … WITH CHECKSUM explicitly within the TSQL code.

How to determine whether CHECKSUM was enabled during database backups?

Method 1: Using System Table

If CHECKSUM was used during database backup then “HAS_BACKUP_CHECKSUMS” column in MSDB..BACKUPSET system table will have value as 1.

/* Verify Whether Backup Was Performed Using CHECKSUM */

Use msdb
GO

SELECT database_name, has_backup_checksums FROM backupset
GO

Method 2: Using RESTORE HEADERONLY command

If the database was backed up using CHECKSUM then HasBackupChecksums flag will have value as 1.

RESTORE HEADERONLY
FROM DISK = 'X:\Backups\AdventureWorks.BAK'
WITH CHECKSUM
GO

How to determine whether CHECKSUM was enabled during database backups

However, CHECKSUM option doesn’t identify In Memory corruption issues. Its only verifies that no corruption occurred between writing the pages to the disk and reading the pages back from the disk. In order to ensure that data pages are corruption free you should always run DBCC CHECKDB on a regular basis. SQL Server will stop the database backup operation and will report the below mentioned error message whenever, Page CHECKSUM validation fails during the backup operation.

Msg 3043, Level 16, State 1, Line 1
BACKUP 'database name' detected an error on page (file_id:page_number) in file 'database_file'.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Syntax to perform Full, Differential and Transactional Log backup of database in SQL Server Using CHECKSUM option is mentioned below for your quick reference.

SYNTAX – How to Use BACKUP CHECKSUM in SQL Server

/* Full Backups Using BACKUP CHECKSUM Option in SQL Server */

Use master
GO

BACKUP DATABASE AdventureWorks
TO DISK = 'X:\Backups\AdventureWorks.BAK'
WITH INIT, CHECKSUM
GO

/* Differential Backups Using BACKUP CHECKSUM Option in SQL Server */

Use master
GO

BACKUP DATABASE AdventureWorks
TO DISK = 'X:\Backups\AdventureWorks.DIF'
WITH INIT, DIFFERENTIAL, CHECKSUM
GO

/* Transactional Log Backups Using BACKUP CHECKSUM Option in SQL Server */

Use master
GO

BACKUP DATABASE AdventureWorks
TO DISK = 'X:\Backups\AdventureWorks.DIF'
WITH INIT,CHECKSUM
GO

TSQL Query to Restore Database Using CHECKSUM Feature

/* Restore Full Backups Using BACKUP CHECKSUM Option in SQL Server 2005 and Higher Versions*/

Use master
GO

RESTORE DATABASE AdventureWorks
FROM DISK = 'X:\Backups\AdventureWorks.BAK'
WITH CHECKSUM
GO

Next Steps

Last Updated On: Nov 07, 2014

Share this Article

Receive Free SQL Server Tips and Keep Learning

Get Free SQL Server Tips