Rajendra Gupta

How to recognize corrupted SQL backup files

February 22, 2019 by

A Database administrator’s key task is to keep the database healthy and available for the users. We are used to taking regular SQL backups depending upon the database criticality and the recovery model. We define the Recovery Point Objective (RPO) Recovery Time Objective (RTO) or the database system, and we should be able to recover the database in any scenario to meet the requirement.

We have the following backup types available in SQL Server.

  • Simple Recovery Model: Full and differential backup
  • Full Recovery model: Full, differential and log backup

Consider a scenario in which we are taking regular database backups. Due to some unexpected issues such as database corruption, server or hard disk failure, the database is down. In this case, we want to restore the last full SQL backup to recover the database. You cannot restore the database backup because the backup file is corrupt. If we cannot restore the backup during such critical scenarios, there is no use of taking backups in the first place. If we do not have a valid database backup in a disaster scenario, we might lose data since the previous successful valid backup.

In this article, we will use a different way to identify and be sure that our backups are healthy. This way we can ensure that our environment will be able to recover in case of any unexpected downtime.

Verify Database Backups using SQL Server Management Studio

Let us look at the steps to take full backup using SSMS. Right click on the database -> Backups:

In the first page, we define the SQL backup type and the backup file location:

In the ‘Media Options’ page, you can see a section for the ‘Reliability’:

We have the following options under ‘Reliability’.

  • Perform checksum before writing to the media: We can ensure a good SQL backup using this option. SQL Server internally calculates the checksum value for each page. This checksum value remains precisely the same for the same data in the page during the recalculation as well. SQL Server writes the CHECKSUM value for each page during the backup once we select this option.

    During the database restore process, SQL Server recalculates the CHECKSUM value for each page and matches with the CHECKSUM value written during the backup. If both values are similar, it shows that the database backup is valid.

Let us put a checkbox against this option and generate the SQL backup database script:

It generates the below script. You can see a parameter CHECKSUM in the SQL backup database command.

Once you have executed the backup, you can still verify whether the backup taken earlier consists of a CHECKSUM or not.

In the following screenshot, we can see the backup set has value 1 and 0.

  • Value 1 for has_backup_checksum: It shows database backup performed with CHECKSUM
  • Value 0 for has_backup_checksum: It shows database backup did not consist of CHECKSUM information

We can enable the trace flag 3023 to take all backups using CHECKSUM. If we enable this trace flag at a global level, we do not need to specify this option explicitly while taking the SQL backup.

Command to enable trace flag at a global level.

In SQL Server 2014 or later, we need to enable backup checksum default in the sp_configure configuration options. Execute the below query in SSMS.

You get the option ‘backup checksum default’ in the sp_configure command output:

We have the following configuration options.

  • 1: Value ‘1’ shows that SQL backups with CHECKSUM is enabled at the instance level
  • 0: Value ‘0’ shows that this setting is not active. It is the default configuration

Use the below command to enable this setting at the instance level.

In the following screenshot, you can see that the ‘backup checksum default’ option is enabled at the instance level using the sp_configure command:

Verifying the SQL backup when finished

We have another option ‘Verify backup when finished’ in the backup wizard of SSMS. It checks for the following parameters.

  • It checks whether a SQL backup file is accessible or not
  • It reads the header information in the SQL backup set and validates the information

It does not restore the database but instead validates the metadata. Therefore, let us put a check in front of ‘Verify backup when finished’:

You get the below command once you script out using SSMS script actions window:

This time the database backup command is divided into two parts.

  1. Query to take the database backup: This section includes the backup database command.
  2. SQL Backup verification using the ‘RESTORE VERIFYONLY’: This section includes the command for the backup verification and raise error if any.

SQL Server takes the backup in the Microsoft Tape Format (MTF). If we take the database backup with only ‘verify backup when finished’ option, it only performs the few checks on the backup set blocks in MTF. It does not check for the actual data blocks. Therefore, as per best practice, we should combine both the option together as per the following image and execute the backup:

We can see the below script with the CHECKSUM in backup command and RESTORE VERIFYONLY in the verification command.

Once you execute the backup, in the output, you get a message stating backup file is valid or not:

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

Related posts:

  1. SQL interview questions on database backups, restores and recovery – Part III
  2. SQL interview questions on database backups, restores and recovery – Part II
  3. SQL Restore Database using DBATools
  4. SQL interview questions on database backups, restores and recovery – Part I
  5. Understanding SQL Server Backup Types
168 Views