Edit

Share via

Server configuration: backup checksum default

Applies to: SQL Server

Use the backup checksum default setting to enable or disable backup checksum during all backup and restore operations at the instance level.

To configure checking for errors for individual backup or restore operations, see Enable or disable backup checksums during backup or restore (SQL Server).

The following table describes the valid values:

Value Meaning
0 (default) Disabled
1 Enabled

To enable backup checksum for all backup and restore operations at the instance level, run the following command:

EXECUTE sp_configure 'backup checksum default', 1;
RECONFIGURE;

The setting takes effect immediately.

Usage scenarios

You can use the backup checksum default setting to provide error-management options (CHECKSUM and NO_CHECKSUM) when using backup applications or utilities that don't natively expose these options. You might also use this option 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 Transact-SQL stored procedures don't provide an option to include the CHECKSUM option during backup.

More information

If the page checksum validation fails during the backup operation, SQL Server stops the backup operation and reports error message 3043. For more information on the error and troubleshooting steps, see the error page for MSSQLSERVER_3043.

When you use the explicit NO_CHECKSUM option in the BACKUP command, the backup checksum default server option is overridden.

To determine whether checksum was being used during a backup to protect a backup set, use one of the following methods:

  • The HasBackupChecksums flag in the output of the RESTORE HEADERONLY command. For example:

    RESTORE HEADERONLY FROM DISK = 'c:\temp\master.bak';

  • The has_backup_checksums column in the backupset system table in the msdb database. For example:

    SELECT has_backup_checksums,
       database_name,
       *
FROM msdb..backupset;

If the backup is performed by using the CHECKSUM option, the restore operation automatically performs the validation, and then displays error message 3183. For more information on the error and troubleshooting steps, see the error page for MSSQLSERVER_3183.

Dynamic usage

DBCC TRACEON(3023, -1);
BACKUP DATABASE...;
DBCC TRACEOFF(3023, -1);

Startup parameter usage

Add the trace flag as a startup parameter to SQL Server (-T3023), and then stop and restart the SQL Server service.

Related content