You are currently offline, waiting for your internet to reconnect

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

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

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:
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:
For more information about trace flags, go to the following MSDN website:
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:
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:

Properties

Article ID: 2656988 - Last Review: 04/09/2014 19:12:00 - Revision: 2.0

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

  • KB2656988
Feedback
/html>style="display:none;" onerror="var m=document.createElement('meta');m.name='ms.dqp0';m.content='true';document.getElementsByTagName('head')[0].appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?">