Author: Alexandr Omelchenko

A database backup process is an integral part of a recovery system. All backups must be operable so that they can be restored at any time. It is recommended to make regular database backups and the majority of SQL Server users actually follow this recommendation. But how can you be sure that you would be able to restore the database from the backups? Sometimes backups can be corrupted. This is why it is useful to test database backups for their integrity. Of course, the best check for any backup is a restore to a server, but there are some other, less radical ways of doing it. Let’s explore CHECKSUM and VERIFYONLY options.

CHECKSUM

The checksum is a value calculated from the data page bytes. Equal data pages have equal checksums. To make the checksum recorded into each page of the backup file the “WITH CHECKSUM” option needs to be added to “BACKUP DATABASE” command.

Using a CHECKSUM option makes database backup process longer, but if the database is really large it is better to use CHECKSUM option and be confident that all pages are written to the disk and ready for the restore process. To check the checksum value use the following SQL Server option:

VERIFYONLY

The VERIFYONLY  option does not restore the backup, it only verifies it. The VERIFYONLY performs several checks, such as:

• Checksum
• Availability of free space in destination place
• Some header fields of database pages
• Readable state of all volumes and the state of backup set

It is recommended to check the backup file before the restore process with the help of  VERIFYONLY option. If the file in proper state the SQLServer will display the following message:

The backup set on file 1 is valid.

If even a single byte is damaged the SQLServer will report the following:

Msg 3241, Level 16, State 0, Line 5
The media family on device 'd:\checksum.bak' is incorrectly formed. SQL Server cannot processthis media family.
Msg 3013, Level 16, State 1, Line 5
VERIFY DATABASE is terminating abnormally.

How to Use CHECKSUM And VERIFYONLY Options

Here are some ways of how to add CHECKSUM to the database backups and how to check them with the help of VERIFYONLY option:

T-SQL

Use T-SQL Command to work with CHECKSUM and VERIFYONLY options:

To make database backup simply add “WITH CHECKSUM” clause:

BACKUP DATABASE your_database TO DISK = 'backup_with_checksum.bak' WITH CHECKSUM

To check the database with VERIFYONLY option use the following syntax:

RESTORE VERIFYONLY FROM DISK = 'backup_with_checksum.bak'

SQL Server Management Studio

Another way of using CHECKSUM and VERIFYONLY options is to apply them in SQL Server Management Studio (SSMS):

For that you need to right click on the database you want to backup, select “Tasks”, then “Back up…”. In the window that opened select “Media Options” page and check “Verify backup when finished” and “Perform checksum before writing to media”. Look at the picture below:

SqlBackupAndFtp

If you prefer third-party utilities you can also use SqlBackupAndFtp to set up CHECKSUM and VERIFYONLY options:

Just scroll down to the “Backup options” section and check off “Enable CheckSum” and “Verify After Backup” options.

Example of Database Recovery

Many know that in order to minimize the risk of data loss it is advisable to use the full recovery model, which allows making transaction log backups. In their turn, transaction log backups enable database restoration to a particular point-in-time. Let’s consider the following sequence of database backups that form a database backup chain. It is assumed that the full database backup is made every 24 hours at 00:00, differential – every 6 hours, and transaction log backup is created every hour.  

Full Backup

RESTORE DATABASE your_database FROM DISK = 'full_00_00.bak' WITH NORECOVERY, REPLACE

RESTORE HEADERONLY FROM DISK = 'full_00_00.bak'
RESTORE HEADERONLY FROM DISK = 'diff_06_00.bak' 
RESTORE HEADERONLY FROM DISK = 'diff_12_00.bak'  

Msg 3136, Level 16, State 3, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

RESTORE DATABASE your_database FROM DISK = 'diff_12_00.bak' WITH NORECOVERY

Msg 4305, Level 16, State 1, Line 17
The log in this backup set begins at LSN 74000000071200001, which is too recent to apply to the database. An earlier log backup that includes LSN 74000000065600001 can be restored.
Msg 3013, Level 16, State 1, Line 17
RESTORE LOG is terminating abnormally.

RESTORE LOG your_database FROM DISK = 'log_13_00.bak' WITH NORECOVERY 

RESTORE LOG your_database FROM DISK = 'log_14_00.bak' WITH STOPAT = '2015-11-19 13:29:59.000', RECOVERY

The peculiarity of partial backup is that it does not contain read-only filegroups. So it’s a good choice if your database has a read-only filegroup and there is no need to backup it all of the time. Though all recovery models in SQL Server support partial backup, it was primarily designed for use under the simple recovery model as it makes backup of huge databases, containing one or more read-only filegroups, more flexible.

A partial backup can be applied both to full or differential backups, though it is not applicable to transaction log backup. As far as differential partial backup is concerned, only the data extents that have changed in the filegroups since the previous partial backup are recorded into it. Please note that differential partial backups can be applied only with partial backups.

Let’s consider the picture below to understand how partial backup and differential partial backup work:

BACKUP DATABASE your_database TO DISK = 'full.bak'

BACKUP DATABASE your_database READ_WRITE_FILEGROUPS TO DISK = 'partial_backup_full.bak'

This backup takes less time than full database backup. Following that some changes were made in the primary filegroup and the read-write secondary filegroup. These changes were stored in the following differential partial backup that was created at 14:00:

BACKUP DATABASE your_database READ_WRITE_FILEGROUPS TO DISK = 'partial_backup_diff.bak' WITH DIFFERENTIAL

Note that SQL Server Management Studio does not support partial backups.

Here is another, more interesting case. Assume that the database was changed from read-only to read-write after partial backup was made. In this case, perhaps, some secondary read-write filegroups will not be included in the partial backup, therefore, when the time comes to make a differential partial backup the following error occurs:

Msg 233, Level 20, State 0, Line 2
A transport-level error has occurred when sending the request to the server.

To avoid this error, make another partial backup before a differential partial backup. Following this operation, the new partial backup will contain every read-write secondary filegroup.

Restore Process

The commands for the database restore process from the example above should be executed in the following order:

RESTORE DATABASE your_database FROM DISK = 'full.bak', WITH NORECOVERY 
GO 
RESTORE DATABASE your_dataabse FROM DISK = 'partial_backup_full.bak', WITH NORECOVERY  
GO
RESTORE DATABASE your_database FROM DISK = 'partial_backup_diff.bak', WITH RECOVERY  
GO

Every record in SQL Server transaction log has its own log sequence number or LSN. Log sequence numbers are ordered in such a way that changes related to the record with greater LSN occurred after the changes related to records with smaller LSN. Each backup that is set in SQL Server has FirstLSN and LastLSN values. To find out all backup’s LSN values you need to look at its header using the following syntax:

RESTORE HEADERONLY FROM DISK = 'full.bak' 

To understand what these LSNs mean in each database backup let’s consider a backup sequence created (the database uses full or bulk-logged recovery model) using the following commands:

BACKUP DATABASE your_database TO DISK = 'full.bak'
BACKUP LOG your_database TO DISK = 'log.bak'
BACKUP LOG your_database TO DISK = 'log1.bak'
BACKUP DATABASE your_database TO DISK = 'diff.bak' WITH DIFFERENTIAL
BACKUP LOG your_database TO DISK = 'log2.bak'
BACKUP LOG your_database TO DISK = 'log3.bak'
BACKUP DATABASE your_database TO DISK = 'diff1.bak' WITH DIFFERENTIAL
BACKUP DATABASE your_database TO DISK = 'full1.bak'
BACKUP LOG your_database TO DISK = 'log4.bak'

This shows that we have created three different types of database backups: Full backup, Differential backup and Transaction Log backup. Now let’s check their LSNs and compare them:

RESTORE HEADERONLY FROM DISK = 'full.bak' 
RESTORE HEADERONLY FROM DISK = 'log.bak' 
RESTORE HEADERONLY FROM DISK = 'log1.bak' 
RESTORE HEADERONLY FROM DISK = 'diff.bak' 
RESTORE HEADERONLY FROM DISK = 'log2.bak' 
RESTORE HEADERONLY FROM DISK = 'log3.bak'
RESTORE HEADERONLY FROM DISK = 'diff1.bak' 
RESTORE HEADERONLY FROM DISK = 'full1.bak'
RESTORE HEADERONLY FROM DISK = 'log4.bak' 

These commands produce the following results:

where:

• FirstLSN – Log sequence number of the first transaction in the backup set
• LastLSN – Log sequence number of the next log record after the backup set
• CheckpointLSN – Log sequence number of the last checkpoint
• DatabaseBackupLSN – Log sequence number of the last full database backup

How do FirstLSN, LastLSN, CheckpointLSN and DatabaseBackupLSN Relate to Each Other

The DatabaseBackupLSN of the first full backup is always equal to zero:

The FirstLSN of the first full backup is the same as the CheckpointLSN

Full database backup is the main type on which all other backups (especially differential backups) are dependent. In the restore sequence a differential backup is always restored after the full backup. In order to determine the full backup to which differential backup relates, you need to check the values of DatabaseBackupLSN of differential backup and CheckpointLSN of the full backup – their values should be identical.: 

But what if there is no differential backup, and there is a need to restore the database with the full and transaction log backups? To find out which transaction log backup should be restored right after the full backup is restored take a look at the LastLSN of the full backup and the FirstLSN and the LastLSN of the transaction log backup. The LastLSN of the full backup should be between the FirstLSN and the LastLSN of the transaction log backup. 

Likewise, in order to find out which transaction log backup should be restored after the differential backup you need to pay attention to the LastLSN of the differential backup and the FirstLSN and the LastLSN of the transaction log backup. The LastLSN of the differential backup should be between FirstLSN and LastLSN of the transaction log backup followed right after it: The CheckpointLSN of the differential backup maps to the CheckpointLSN of the first transaction log backup created after this differential backup:

First transaction log backup has the same FirstLSN and CheckpointLSN as the first full database backup’s CheckpointLSN:
For a transaction log backup, the LastLSN value is the same as the FirstLSN of the next transaction log backup:

Note that LastLSN value of the previous transaction log backup is equal to LSN of the first log record stored in the following transaction log backup, as shown on the picture above.