Skip to content

backup-checksum-default.md

Latest commit

 

History

History
80 lines (54 loc) · 3.83 KB

backup-checksum-default.md

File metadata and controls

80 lines (54 loc) · 3.83 KB
title Server Configuration: backup checksum default
description Find out about the backup checksum default option. See how to use it to turn backup checksum on or off during SQL Server backup and restore operations.
author rwestMSFT
ms.author randolphwest
ms.date 08/26/2025
ms.service sql
ms.subservice configuration
ms.topic how-to

Server configuration: backup checksum default

[!INCLUDE 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 [!INCLUDE ssnoversion-md] log shipping or the Backup database task from [!INCLUDE ssnoversion-md] 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, [!INCLUDE ssnoversion-md] 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 [!INCLUDE ssnoversion-md] (-T3023), and then stop and restart the [!INCLUDE ssnoversion-md] service.

Related content