Connect With MyTechMantra.com













"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."






Enable BACKUP CHECKSUM in SQL Server Using Trace Flag 3023


Nov 04, 2014


Introduction

This article explains the steps to enable BACKUP CHECKSUM default configuration option using Trace Flag 3023 at instance level in SQL Server 2005 and higher versions. Once Trace Flag 3023 is turned on, CHECKSUM option is automatically enabled for all the BACKUP or RESTORE commands. The advantage of turn on this trace flag is you don’t need to rewrite all your existing backup scripts. Moreover, once you enable trace flag 3023 SQL Server Maintenance Plans or Third Party Tools can leverage CHECKSUM option to Perform Full, Differential or Transactional Log backups in SQL Server.

How CHECKSUM Option Works in SQL Server?

During Database Backup Operation when CHECKSUM option is used SQL Server whenever writes a data page to disk it will calculate a CHECKSUM and store the value in the Page Header. Later on whenever the page is read back into memory, SQL Server will recalculate the CHECKSUM value and compare it with the value stored in the Page Header. When the value matches, it means there are no I/O specific corruption issues.

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 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

Different Methods to Configure Trace Flag 3023 in SQL Server

DBA can configure trace flag in multiple ways:-

  • Configure Trace Flags Using DBCC TRACEON Command in SQL Server
  • Configure Trace Flags Using Startup Parameter Option in SQL Server

Permissions required to enable or disable trace flags in SQL Server

You must be a membership of sysadmin fixed server role in SQL Server if you want to enable or disable trace flags.

Configure Trace Flags Using DBCC TRACEON Command in SQL Server

/* Turn ON Trace Flag 3023 Using DBCC TRACEON Command */

DBCC TRACEON (3023,-1)
GO

/* Turn OFF Trace Flag 3023 Using DBCC TRACEOFF Command */

DBCC TRACEOFF (3023,-1)
GO

Important Note

However, once you restart SQL Server the trace flag is no longer available. If you need the trace flag to be available after the reboot then you will have to set it as a Startup Parameter.

Configure Trace Flags Using Startup Parameter Option in SQL Server

DBA can add the trace flag as a startup parameter to SQL Server (-T3023). However, in order to use the trace flag DBA will have to stop and then restart the SQL Server Service. For more information on how to set trace flags as startup parameter see, Improved Startup Parameters in SQL Server 2012.

How to Override Trace Flag 3023 in SQL Server

One can override Trace Flag 3023 when you explicitly use NO_CHECKSUM option in Database BACKUP or RESTORE command.

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

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

Next Steps



Share this Article

LEARN MORE...
 
Winners
White Papers
Product Reviews
Trending News
All Articles
Free Tools
 
Follow Us...