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.

20 comentarios:

Anónimo dijo...

Gracias! Very helpful.

Unknown 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


Unknown 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

Unknown 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

Unknown 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

Unknown 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/

Unknown 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.

Unknown 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/

Unknown dijo...

Get 24*7 SQL Server DB Recovery with Cognegic’s Exchange Database Recovery
Are you looking for restoring your SQL server with full back up? Or need to make any changes in backup plan? If yes, then pull your shocks up and get connected with world-class RIM Support provides i.e. Cognegic’s DB Recovery Support or DB Recovery Services. We take guaranteed best Backup Recovery and full support. You can contact to our professional experts through this number 1-800-450-8670 anytime and any day. Our Database Configuration Support is very affordable.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

Unknown dijo...

How to Recover SQL Database through DB Recovery Support
Mostly people asked to recover an Oracle database if it permanently deleted by mistake. If these kinds of question are roaming in your mind then you are at correct platform. You can simply recover your data by manually. You can recover database by database mirror, full database backup stored where your admin chose to be stored. But apart from that you can quickly recover your data by DB Recovery Service or Exchange Database Recovery. At Cognegic, we provide Online Database Management Support to solve your recovery related issue.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

Unknown dijo...

The most effective method to Solve Common SQL Server Restore Issue through DB Recovery Support
The Database reinforcement and reestablish is an exceptionally basic and fundamental assignment for any engineer. You have the capacity and ability to reestablish the lost information and data which by erroneously erased by you. Truly, this issue reclamation process takes long time or requires heaps of specialized aptitudes yet in the event that you are not ready to recuperate your information then most likely you will be let go from the activity. In any case, let us reveal to you that we at Cognegic give DB Recovery Services or Online Database Management Support for those engineers who can't perform reinforcement and recuperation. Thus, you can pick our Exchange Database Recovery procedure to get back your basic information.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

Anchal Vashist dijo...

Hi,
Your article is too good and It has a lot of helpful information, I really like it. I also have a helpful URL about Facebook Password Recovery, If you have any query then call Facebook Customer Support Number. I hope you like It.
Thank You!

sofia dijo...
Este comentario ha sido eliminado por el autor.
sofia dijo...

Facebook big social media website where you can connect from your friends and chat with the team. Here you can share your video and think. If you are facing some technical error then connect our Facebook Customer service team.

Facebook Customer Service

Facebook Support Number

Facebook Help Number

Facebook Phone Number

Facebook Technical Support

Facebook Customer Service

Facebook Phone Number

Facebook Help Number

kristinahojholt dijo...

Incredible! This blog looks exactly like my old one! It's on a totally different topic but it has pretty much the same layout and design. Wonderful choice of colors! epicor reports

Facebook Customer Care Service dijo...

About Face book is a global network his follower is all over the worlds, at the time Facebook is social site as well as business advistsment station. Facebook related any type of issue facebook offers permanent technical service Dial Facebook Customer Service Number 1877-323-8313

Techystick dijo...

class 10 tution classes in gurgaon

pragyan dijo...
Este comentario ha sido eliminado por el autor.
sdgds dijo...

kralbet
betpark
tipobet
slot siteleri
kibris bahis siteleri
poker siteleri
bonus veren siteler
mobil ödeme bahis
betmatik
6V1U

Kenau dijo...

slot siteleri
kralbet
betpark
tipobet
betmatik
kibris bahis siteleri
poker siteleri
bonus veren siteler
mobil ödeme bahis
2ZY7W

Publicar un comentario