How to enable the CHECKSUM option if backup utilities do not expose the option

Article translations Article translations
Article ID: 2656988 - View products that this article applies to.
Expand all | Collapse all

Summary

The Transact-SQL BACKUP and RESTORE commands provide the error-management options CHECKSUM and NO_CHECKSUM. If you are using backup applications or utilities that do not expose these options, you can enable the CHECKSUM option by using Trace Flag 3023 on the instance of Microsoft SQL Server. If Trace Flag 3023 is turned on, the CHECKSUM option is automatically enabled for the BACKUP command. You can turn on Trace Flag 3023 to make sure that all backups use the backup CHECKSUM option. If you do this, you do not have to rewrite all the existing backup scripts.

More information

You might have to use Trace Flag 3023 when you use utilities such as SQL Server log shipping or the Backup database task from SQL Server maintenance plans. These utilities and the associated TSQL stored procedures do not provide an option to include the CHECKSUM option during backup.

When you use the CHECKSUM option during a backup operation, the following processes are enabled:
  • Validation of page checksum if the database has the PAGE_VERIFY option set to CHECKSUM and the database page was last written by using checksum protection. This makes sure that the data that is backed up is in a good state.
  • Generation of a backup checksum over the backup streams that are written to the backup file. During a restore operation, this makes sure that the backup media itself was not damaged during file copy or transfers.

If the page checksum validation fails during the backup operation, SQL Server stops the backup operation and reports the following error:
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.

When you use the explicit NO_CHECKSUM option in the BACKUP command, Trace Flag 3023 behavior is overridden. To determine whether checksum is helping to protect a backup set, use one of the following:
  • The HasBackupChecksums flag in the output of the RESTORE HEADERONLY command
  • The has_backup_checksums column in the backupset system table in the msdb database

If the backup is performed by using the CHECKSUM option, the restore operation automatically performs the validation and then reports problems by using error messages that resemble the following:
Msg 3183, Level 16, State 1, Line 1
RESTORE detected an error on page (1:243) in database "corruption_errors_test" as read from the backup set.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Trace Flag 3023 can be used dynamically by using a DBCC TRACEON statement. Or, Trace Flag 3023 can be used as a startup parameter.
Dynamic usage
DBCC TRACEON(3023,-1)

BACKUP DATABASE…

DBCC TRACEOFF(3023,-1)

For more information about DBCC TRACEON, go to the following Microsoft Developer Network (MSDN) website:
DBCC TRACEON (Transact-SQL)
Startup parameter usage
Add the trace flag as a startup parameter to SQL Server (-T3023), and then stop and then restart the SQL Server service.

For more information about Startup options, go to the following MSDN websites:
Using the SQL Server Service Startup Options
How to: Configure Server Startup Options (SQL Server Configuration Manager)

For more information about trace flags, go to the following MSDN website:
Trace Flags (Transact-SQL)
New in SQL Server 2014
SQL Server 2014 supports the new backup checksum default configuration option that you can use to control the backup CHECKSUM option. The details are as follows:

Name: backup checksum default
Minimum: 0
Maximum: 1
Default value: 0
Usage: sp_configure backup checksum default
Returns:
Collapse this tableExpand this table
NameMinimumMaximumconfig_valuerun_value
backup checksum default0100
SQL Server versions
This information applies to the following versions of SQL Server:
  • SQL Server 2005
  • SQL Server 2008
  • SQL Server2008 R2
  • SQL Server 2012
  • SQL Server 2014
Backup utilities
For more information about backup utilities, go to the following MSDN websites:

Back Up Database Task (Maintenance Plan)

Log Shipping Deployment

Properties

Article ID: 2656988 - Last Review: April 9, 2014 - Revision: 2.0
Applies to
  • Microsoft SQL Server 2014 Enterprise
  • Microsoft SQL Server 2014 Developer
  • Microsoft SQL Server 2014 Express
  • Microsoft SQL Server 2014 Standard
  • Microsoft SQL Server 2014 Web
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Express
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2012 Web
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Express
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Workgroup
  • Microsoft SQL Server 2008 R2 Web
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Express
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Compact Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Evaluation Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Keywords: 
KB2656988

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com