SQL SERVER – Working with LSN for Restore and Backup Databases. (Error: Msg 4305, Level 16, State 1, Line 1)

Today in my job, some colleague asks me about the next error:

Msg 4305, Level 16, State 1, Line 1

The log in this backup set terminates at LSN 9386000024284900001, which is too early to apply to the database. A more recent log backup that includes LSN 9417000002731000001 can be restored.

So, my explanation was:

You are trying to apply a Log file that finish with LSN 9386000024284900001 into a DB restored with LSN 9417000002731000001, so, the LSN of the Full Backup (restored) is older than the log file that is trying to apply. So, the log file is not possible to apply it.

Now, we are going to replicate that error and we are going to see how to know the LSN of each backup, in order to clarify and understand the error mentioned above.

First a DB will be created, some backups will be taken, then the DB will be restored and some log will be applied in the wrong way, then we are be able to identify the right way and the we will apply the logs.

First, we create a database and a table for testing purpose.

--Create DB for Testing

create database DB_TestLSN

GO

USE DB_TestLSN

GO

--create a testing table.

create table test(

a int

)

Now, we are going to backup the database and log:

backup database DB_TestLSN

to disk='c:\DB_TestLSN.bak'

A table2 and table3 are generated and two log backups are taken:

create table test2(

a int

)

-- backup log file

backup log DB_TestLSN

to disk='c:\DB_TestLSN_LOG1'

create table test3(

a int

)

-- backup log file

backup log DB_TestLSN

to disk='c:\DB_TestLSN_LOG2'

Restoring database….

First restore the full backup

restore database DB_TestLSN_REST

from disk='c:\DB_TestLSN.bak'

with norecovery

then we restore intentionally the wrong log file

restore log DB_TestLSN_REST

from disk='c:\DB_TestLSN_LOG2'

Msg 4305, Level 16, State 1, Line 1

The log in this backup set begins at LSN 18000000015300001, which is too recent to apply to the database. An earlier log backup that includes LSN 18000000014000001 can be restored.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

… is necessary to identify what is the correct way to restore the logfiles. For this, we can mention two manners:

1. If we have access to the server where the backup was taken.

We can check the system table msdb..backupset. To identify the chronologic order of the data backup and log backup, we can put special attention on the following columns

• backup_start_date, backup_finish_date, first_lsn, last_lsn

With that columns we can view the LSN of each of the backups taken, so our error shows that 18000000015300001 (this is the first lsn for the second log backup) is too early to apply and and earlier log backup that includes LSN 18000000014000001 must be exists, and that is correct, the first log backup includes the LSN 18000000014000001 and also this logs begins with the log_chain.

select database_name, type, first_lsn, last_lsn ,checkpoint_lsn ,database_backup_lsn

from msdb..backupset where database_name = 'DB_TestLSN'

database_name type first_lsn last_lsn begins_log_chain

DB_TestLSN D 18000000007400155 18000000014000001 0

DB_TestLSN L 18000000007400155 18000000015300001 1

DB_TestLSN L 18000000015300001 18000000015900001 0

2. If we have just the backup files.

To know the LSN of the backups we can use

RESTORE HEADERONLY from disk='c:\DB_TestLSN.bak'

RESTORE HEADERONLY from disk='c:\DB_TestLSN_LOG1'

RESTORE HEADERONLY from disk='c:\DB_TestLSN_LOG2'

In this case also is necessary to put special attention on the columns first_lsn, last_lsn in each of the backup files.

To reproduce exactly the error mentioned on the top of this article,

Msg 4326, Level 16, State 1, Line 2

The log in this backup set terminates at LSN 18000000015400001, which is too early to apply to the database. A more recent log backup that includes LSN 18000000022000001 can be restored.

Msg 3013, Level 16, State 1, Line 2

RESTORE LOG is terminating abnormally.

follow up the next steps.

1. Create DB
2. Backup Full Database (BkpDBFULL1)
3. Backup Log (BkpLOG1)
4. Backup Full Database (BkpDBFULL2)
5. Restore BkpDBFULL2
6. Try to apply BkpLOG1

Instead of use backup log, you can use backup incremental and the behavior is the same than we show in this examples.

[Questions & Answers]

Q: What is a LSN (Log Secuence Number): ?

A:

Every record in the Microsoft SQL Server transaction log is uniquely identified by a log sequence number (LSN). LSNs are ordered such that if LSN2 is greater than LSN1, the change described by the log record referred to by LSN2 occurred after the change described by the log record LSN.

The LSN of a log record at which a significant event occurred can be useful for constructing correct restore sequences. Because LSNs are ordered, they can be compared for equality and inequality (that is, <, >, =, <=, >=). Such comparisons are useful when constructing restore sequences.

LSNs are used internally during a RESTORE sequence to track the point in time to which data has been restored. When a backup is restored, the data is restored to the LSN corresponding to the point in time at which the backup was taken. Differential and log backups advance the restored database to a later time, which corresponds to a higher LSN.