SQL SERVER – Error: Msg 4305, Level 16, State 1 – The log in this backup set terminates at LSN, which is too early to apply to the database.

While working with transaction log backup restore or log shipping, you must have seen below error message.

Msg 4305, Level 16, State 1, Line 47
The log in this backup set begins at LSN 33000000048000001, which is too recent to apply to the database. An earlier log backup that includes LSN 33000000044800001 can be restored.
Msg 3013, Level 16, State 1, Line 47
RESTORE LOG is terminating abnormally.

Let’s have a look at the cause of this message. Before that let’s have our sample database ready with backups so that we can play with them.

--Create an empty database
CREATE DATABASE SQLAuthority
GO
-- use the database
USE SQLAuthority
GO
--create first test table.
CREATE TABLE MyTestTable1 (iCol INT)
-- perform full backup F1
BACKUP DATABASE SQLAuthority TO DISK = 'c:\SQLAuthority_F1.bak' WITH FORMAT
--create second test table.
CREATE TABLE MyTestTable2 (a INT)
-- perform transaction log backup T1
BACKUP LOG SQLAuthority TO DISK = 'c:\SQLAuthority_T1.trn' WITH FORMAT
--create third test table.
CREATE TABLE MyTestTable3 (a INT)
-- perform transaction log backup T2
BACKUP LOG SQLAuthority TO DISK =  'c:\SQLAuthority_T2.trn' WITH FORMAT
--create forth test table.
CREATE TABLE MyTestTable4 (a INT)
-- perform full backup F2
BACKUP DATABASE SQLAuthority TO DISK = 'c:\SQLAuthority_F2.bak' WITH FORMAT


Now, we have backups taken in below order:

1. Full back up ‘c:\SQLAuthority_F1.bak’
2. First Transaction log backup ‘c:\SQLAuthority_T1.trn’
3. Second Transaction log backup ‘c:\SQLAuthority_T2.trn’
4. Full back up ‘c:\SQLAuthority_F2.bak’

Reproduce the Error

-- Let’s perform restore now but before that, lets drop the database
USE MASTER
GO
ALTER DATABASE SQLAuthority SET single_user WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE SQLAuthority
GO
RESTORE DATABASE SQLAuthority FROM DISK = 'c:\SQLAuthority_F1.bak' WITH NORECOVERY
GO
RESTORE LOG SQLAuthority FROM DISK = 'c:\SQLAuthority_T2.trn' WITH NORECOVERY
GO


Here is the error.

Processed 280 pages for database ‘SQLAuthority’, file ‘SQLAuthority’ on file 1.
Processed 6 pages for database ‘SQLAuthority’, file ‘SQLAuthority_log’ on file 1.
RESTORE DATABASE successfully processed 286 pages in 0.148 seconds (15.097 MB/sec).
Msg 4305, Level 16, State 1, Line 43
The log in this backup set begins at LSN 33000000026400001, which is too recent to apply to the database. An earlier log backup that includes LSN 33000000023200001 can be restored.
Msg 3013, Level 16, State 1, Line 43
RESTORE LOG is terminating abnormally.

The error appeared because we have skipped T1. As the message says “An earlier log backup that includes … can be restored”. We can get list of backups taken from MSDB database on source server.

DECLARE @db_name VARCHAR(100)
SELECT @db_name = 'SQLAuthority'
--Get Backup History for required database
SELECT TOP (30) s.database_name
,m.physical_device_name
,CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize
,CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken
,s.backup_start_date
,CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn
,CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn
,CASE s.[type] WHEN 'D'
THEN 'Full'
WHEN 'I'
THEN 'Differential'
WHEN 'L'
THEN 'Transaction Log'
END AS BackupType
,s.server_name
,s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
ORDER BY backup_start_date DESC
,backup_finish_date


We can also get the same information from ERRORLOG as well.

SQL SERVER – Where is ERRORLOG? Various Ways to Find its Location

Here are the sample messages. I have trimmed them for clarity.

2015-08-08 17:49:06.340 Backup Database backed up. Database: SQLAuthority, creation date(time): 2015/08/08(17:49:05), pages dumped: 291, first LSN: 33:136:179, last LSN: 33:232:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘c:\SQLAuthority_F1.bak’}).

2015-08-08 17:49:06.550 Backup Log was backed up. Database: SQLAuthority, creation date(time): 2015/08/08(17:49:05), first LSN: 33:136:179, last LSN: 33:264:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘c:\SQLAuthority_T1.trn’}).

2015-08-08 17:49:06.680 Backup Log was backed up. Database: SQLAuthority, creation date(time): 2015/08/08(17:49:05), first LSN: 33:264:1, last LSN: 33:280:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘c:\SQLAuthority_T2.trn’}).

2015-08-08 17:49:07.110 Backup Database backed up. Database: SQLAuthority, creation date(time): 2015/08/08(17:49:05), pages dumped: 291, first LSN: 33:312:143, last LSN: 33:392:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘c:\SQLAuthority_F2.bak’}).

So, once we find missing transaction log backup, we need to restore them in sequence. Do you have any other trick to solve this issue? If you can share, it would help others as well.
Reference: Pinal Dave (https://blog.sqlauthority.com)