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

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
- Read "How to Use Trace Flag 1222 to Identify Deadlocks in SQL Server"
- Read "Setting PAGE_VERIFY Database Option to CHECKSUM for all Databases in SQL Server"
- Read "Start SQL Server in Single User Mode"
Last Updated On: Nov 07, 2014
|
![]() |
|
Please leave your Valuable Comment or Let us know how this article helped you: