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




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:

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






backup checksum default





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

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

What affected your experience?

Thank you for your feedback!