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.
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.
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
Default value: 0
Usage: sp_configure backup checksum default
Collapse this tableExpand this table
|backup checksum default||0||1||0||0|
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
For more information about backup utilities, go to the following MSDN websites: