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

View products that this article applies to.

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.

↑ Back to the top

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: