Log Sequence Numbers Do Not Match (codes 1811, 1812)

Situation

An MS SQL Server backup plan finished with one of the following warnings:

  • Could not complete differential backup. Log sequence numbers do not match. A full backup was forced for the following databases
  • Could not complete transaction log backup. Log sequence numbers do not match. A full backup was forced for the following databases

Databases the full backup was applied to are listed below the warning (see the drawing below):

Cause

The occurrence of these warnings means that the log sequence numbers (LSNs) in the full database backup and the log sequence numbers in the MS SQL Server transaction log mapping is broken. This means, the restore point could be considered as a valid one. This is a reason to force a new full database backup to have a valid restore point.

If the problem occurs once, the forced full backup is the only solution, no more action needed.

But if the problem persists, have a look to the possible root-causes of the problem.

The following 3 root-causes can be a reason of the situation:

  • Two or more backup plans to back up the same MS SQL Server instance
  • Another MS SQL database backup performed by third-party software
  • MS SQL native backup (maintenance plan)

Two or more backup plans to back up the same MS SQL Server instance

Check whether you have more then one backup plan for the same MS SQL Server instance.

  1. Open the Management Console.
  2. In the Computers menu, select the Remote Management item.
  3. In the computer list, find the computer you where you back up MS SQL Server, then click the Settings icon.
  4. Select Show Plans.
  5. In the side panel, check the backup plan list for the MS SQL Server backup plans.
  6. Click on the plan you have, then click Edit. Check whether you have several backup plans for the same MS SQL Server instance. Refer to Solution 1 to resolve the issue, if any.

Another MS SQL database backup performed by third-party software

How to check: Retrieve a list of backups for a database as described in Retrieving List of Backups Performed For a Specific Database. All backups performed by our software should appear in physical_device_name column in the temporary folder specified on the Advanced tab of the Options dialog of the Backup Agent instance installed on the computer where you back up MS SQL Server.

In case the resulting list contains any backups that appear in another location, refer to Solution 2

MS SQL native backup (maintenance plan)

Refer to Microsoft documentation for details on how to view backup jobs for MS SQL Server.

In case you have a job to back up SQL Server Database, refer to Solution 3

Solutions

Solution 1

It is recommended to create only one backup plan for every SQL Server instance to avoid one plan interfering with another. In case you need to back up the SQL Server data to several locations try to use hybrid backup. Consider the following limitations:

  • Full backup (except for backup with copy-only enabled) will be corrupted and cannot be used for restore if another backup plan create differential backup after it
  • Transaction log backup will be corrupted and cannot be used for restore if another backup plan create transaction log backup after it

To prevent the issue, delete or stop your extra MS SQL Server backup plans or disable the differential backup and transaction log backup schedule in these plans, as described below:

  1. Open the Management Console.
  2. In the Computers menu, select the Remote Management item.
  3. In the computer list, find the computer you where you back up MS SQL Server, then click the Settings icon.
  4. Select Show Plans.
  5. In the side panel, check the backup plan list for the MS SQL Server backup plans.
  6. Edit the required backup plan.
  7. Follow the backup wizard to the Schedule step.
  8. Set the schedule for full backup with the required frequency and disable the differential and transaction log backup schedule.
  9. Follow the backup wizard steps to the end to save the backup plan configuration.

Solution 2

It is recommended to have only one backup plan for every SQL Server instance to avoid one plan interfering with another. In case you are planning to use another backup software, consider the following limitations:

  • Full backup (except for backup with copy-only enabled) will be corrupted and cannot be used for restore if another backup software create differential backup after it
  • Transaction log backup will be corrupted and cannot be used for restore if another backup software create transaction log backup after it You should stop or re-configure all MS SQL database backups performed by third-party software as described in technical documentation for this software. In case you do not want to stop these backups, you can use a workaround described below for the backup plan generating the error.

Solution 3

Disable MS SQL native backup jobs to avoid these jobs interfering with your MS SQL backup plan. Refer to Microsoft documentation for details on how to manage backup jobs for MS SQL Server.

Workaround

You can prevent the backup plan from making any changes to the source database(s). To do this, enable the Copy-only option:

  1. Edit the required backup plan.
  2. Follow the backup wizard to the Select Databases step.
  3. Select the Copy-only check box.
  4. Follows the backup wizard steps to the end to save the backup plan configuration.

As a solution, you can build a full SQL backup strategy. To do this, create a new schedule for the backup plan.

  1. Edit the required backup plan.
  2. Follow the backup wizard to the Schedule step.
  3. Set the schedule for full backup with the required frequency and disable the differential and transaction log backup schedule.
  4. Follow the backup wizard steps to the end to save the backup plan configuration.

Learn about LSNs

A a log sequence numbers (LSNs) are uniquely incremental identifiers for every entry in full, differential and transaction log backups. A higher LSN of the change record indicates a later change.

Some attributes for the full database backup LSNs are:

  • The very first full database backup will always have a DatabaseBackupLSN of zero
  • The very first full database backup's FirstLSN will be the same as the CheckpointLSN

Some attributes of a differential database backup LSNs are:

  • The DatabaseBackupLSN value for the differential backup identifies the full database backup that is required in order to apply the differential database backup
  • The DatabaseBackupLSN value for the differential backup must match its base full database backup CheckpointLSN
  • The CheckpointLSN maps to the CheckpointLSN of the first transaction log backup after the differential backup

Some attributes of a transaction log backup LSNs are:

  • A LSN uniquely and incrementally identifies every record in a transaction log backup
  • The FirstLSN and CheckpointLSN of the first transaction log backup is also the first full database backup CheckpointLSN if the backup is taken when the database is idle and no replication is configured
  • The transaction log LSN chain is not affected by a full or differential database backup
  • LSN are sequential in nature. A higher LSN value indicates a later point in time

Understanding LSN Mapping Examples in SQL Server

Below are 3 sections that describe the LSN mapping for:

  • Full database backup LSN to Transaction Log backup LSN
  • Full database backup LSN to Differential database backup LSN
  • Differential database backup LSN to Transaction Log backup LSN

Full database backup LSN to Transaction Log backup LSN example

FirstLSN identifies the first log record included in the backup. LastLSN includes log records up to, but not including this LSN. When planning which transaction log backup to use to roll forward, the LastLSN + 1 of the Full database backup will fall in between the FirstLSN and LastLSN of its subsequent transaction log backup. In the example above, Full database backup LastLSN 24000000025600001 falls in between transaction log backup T1 FirstLSN 24000000016000100 and LastLSN 24000000028800000. Applying T1 after F1 will succeed, applying T2 or T3 after F1 will result in an error. A transaction log backup's LastLSN is the FirstLSN in the subsequent transaction log backup chain. In the example above, transaction log backup T1 LastLSN 24000000028800000 is the FirstLSN of transaction log backup T2, transaction log backup T2 LastLSN 24000000030400000 is the FirstLSN of transaction log backup T3 and so on.

Full database backup LSN to Differential database backup LSN example

A differential database backup can only be applied ONCE to a restored full database backup that has a CheckpointLSN value that is equal to the differential backup DatabaseBackupLSN.

Differential database backup LSN to Transaction Log backup LSN example

A differential backup LastLSN + 1 will be in between the FirstLSN and LastLSN of its subsequent transaction log backup.

In the example above, full database backup LastLSN 24000000035200001 falls in between transaction log backup T3 FirstLSN 24000000030400000 and LastLSN 24000000036000000. Applying T3 after D1 will succeed, applying T4 or another transaction log backup will result in an error.

To learn more about LSN, read the SQL Server Transaction Log Architecture and Management Guide chapter at docs.microsoft.com