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

Situation

An MS SQL Server backup plan finishes 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 and previous backup type 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 a chain of log sequence numbers (LSNs) in the database backup and/or log sequence numbers in the MS SQL Server backup is broken. This means the restore point cannot be guaranteed as valid. For valid restore point points, a full backup is forced.

If this warning appears the only time, a full backup is enough and no more actions are needed.

If the warning persists, figure out the cause of the warning occurrence.

The three causes that may invoke this warning are listed below:

• There are two or more backup plans that 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 That Back Up the Same MS SQL Server instance

Check if you have more than one backup plan for the same MS SQL Server instance.

In Management Console

Proceed as follows:

1. Open the Management Console.
2. In the Computers menu, select Remote Management.
3. In the computer list, find the required computer, then click the Settings icon.
4. Select Show Plans.
5. In the side panel, view the backup plan list and determine MS SQL Server backup plans.
6. Sequentially view MS SQL plans and check their backup source. If you find plans with the same MS SQL Server instance, read the Solution 1.

In Backup Agent or Backup for Windows

Proceed as follows:

1. Switch to the Backup Plans tab.
2. In the backup plan list, look for MS SQL Server backup plans.
3. Open these plans to manage, expand them, then click Edit.
4. Sequentially view MS SQL plans and check their backup source. If you find plans with the same MS SQL Server instance, read the Solution 1.

Another MS SQL Database Backup Performed by a Third-party Software

Check the list of backups. Proceed as described in the Retrieving List of Backups Performed For a Specific Database article.

All backups performed by MSP360 software should appear in the 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.

If the list contains any backups that appear in another location, refer to Solution 2.

MS SQL native backup (maintenance plan)

To view backups of MS SQL Server, refer to Microsoft documentation.

If you found a native backup, refer to Solution 3.

Solutions

Solution 1

It is recommended to create no more than one backup plan for every SQL Server instance to avoid one plan interfering with another. In case you need to back up SQL Server data to several locations, use the hybrid backup (the backup type with the first backup to the local storage and the subsequent backup to the cloud).

Note the following:

• Differential backup will be corrupted and will not be valid for restore if another backup plan created the full backup before this differential backup. This does not concern full backups with the Copy-only feature enabled
• Transaction log backup will be corrupted and will not be valid for restore if the transaction log backup was created by another backup plan

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:

In Management Console

To edit the backup plan, proceed as follows:

1. Open the Management Console.
2. In the Computers menu, select Remote Management.
3. In the computer list, find the required computer, then click the Settings icon.
4. Select Show Plans.
5. In the side panel, view the backup plan list and determine MS SQL Server backup plans.
6. Click Edit.
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.

In Backup Agent or Backup for Windows

To edit the backup plan, proceed as follows:

1. Switch to the Backup Plans tab.
2. Open the plan to manage, expand it, then click Edit.
3. Follow the backup wizard to the Schedule step, then unselect the Differential backup and the Transaction Log check boxes. This cancels backups of these types.
4. Follow the backup wizard steps to the end to save the backup plan configuration.

To delete the backup plan, proceed as follows:

1. Switch to the Backup Plans tab.
2. Open the plan to manage, expand it, then click Delete.
3. Confirm the backup plan deletion.

Solution 2

It is recommended to create no more than one backup plan for every SQL Server instance to avoid one plan interfering with another. In case you need to back up SQL Server data to several locations, use the hybrid backup (the backup type with the first backup to the local storage and the subsequent backup to the cloud).

Note the following:

• Differential backup will be corrupted and will not be valid for restore if another backup plan created the full backup before this differential backup. This does not concern full backups with the Copy-only feature enabled
• Transaction log backup will be corrupted and will not be valid for restore if the transaction log backup was created by another backup plan

Stop or re-configure all MS SQL database backups performed by third-party software according to guides to this software. In case you need to continue these backups, you can use a workaround described below.

Solution 3

Disable MS SQL native backup to avoid interferences with the MS SQL backup plan that produced this warning.

Read how to manage backup in MS SQL Server in the Microsoft documentation.

Workaround

You can make the backup plan bringing no 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. Follow the backup wizard steps to the end to save the backup plan configuration.

Also, you can build a full SQL backup strategy (making only full backups). 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. Do not the Differential backup and the Transaction log backup schedules.
4. Follow the backup wizard steps to the end to save the backup plan configuration.

Learn about LSNs

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:

• An 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
• LSNs are sequential. 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:

• 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, a 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 invoke the warning.