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.

9 comentarios:

Anónimo dijo...

Gracias! Very helpful.

Kaye R. Jenkins dijo...

Hi Frnds now it is very easy with SQL recovery software, SQL recovery tool and recover your all database file and also you can fix errors from it.

Visit for free demo version:- http://www.recoverydeletedfiles.com/sql-database-recovery-software.html


Alfred Palmer dijo...

You would be able to access and repair corrupt MDF files. This utility software recovery all of tables, views, indexes, stored procedures, unique keys and primary keys from the damaged .MDF files and quick repairing of SQL server database.

Try:- http://www.filesrecoverytool.com/sql-database-repair.html

Richard Wright dijo...

SQL Server Data Recovery software which is an efficient tool to recover corrupt SQL Server database and it doesn't require any technical skill for recovery process. Download this tool from this :- http://www.recoverfilesdata.com/sql-database-mdf-recovery.html

Kaye R. Jenkins dijo...

One more SQL Server Recovery software is also restore all the SQL file objects such as tables, views, triggers, stored procedures, constraints, default constraints, indexes, rules, user defined functions & user defined data types.

Visit: http://www.mannatsoftware.com/stellar-phoenix-sql-server-recovery.html

Henry Gill dijo...

No wonder, MS SQL is well-known database management system that offer reliability and secure features. But in spite of being so popular error continues to frustrate. No matter what's error you can easily fix it as i did. Get more Info:-http://en.mssqldatabaserepair.org/

Johnson Welch dijo...

You can try SysTools SQL Backup Recovery Software which is perfectly helpful in recovering SQL .bak file database. if it is full or differential database backup.

shiv dijo...

Thanks for providing this informative information you may also refer
http://www.s4techno.com/blog/2016/08/02/sql-interview-questions/

Johnson Welch dijo...

Fantastic post, this was very helpful. There I found one more article explaining how to find LSN Number in SQL Server in detail:
http://www.sqlmvp.org/sql-server-log-sequence-numbers-for-backups/

Publicar un comentario