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:
|0||Disabled. This is the default setting.|
The setting takes effect immediately.
You can use the backup checksum default setting to provide error-management options (
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.
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:
HasBackupChecksumsflag in the output of the
RESTORE HEADERONLYcommand. For example:
RESTORE headeronly FROM disk = 'c:\temp\master.bak'
has_backup_checksumscolumn in the
backupsetsystem table in the
msdbdatabase. 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.
SQL Server 2012 and earlier versions
In SQL Server 2012 (11.x) and earlier versions, this option doesn't exist. You need to use Trace Flag 3023 to enable the
CHECKSUM option as a default for the
BACKUP command. Trace Flag 3023 can be used dynamically by using a
DBCC TRACEON statement, or it can be used as a startup parameter.
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.