The log in this backup set begins at LSN, which is too recent to apply to the database

All users who’re working with SQL Server sooner or later face with the following error message:

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

Msg 4305, Level 16, State 1, Line 1

The log in this backup set begins at LSN, which is too recent to apply to the database.

Ok, to solve this issue and understand the reason why it happens, let’s try to reproduce it. Of course, the best way to backup SQL Server database is to use SQLBackupAndFTP, but here we are going to use T-SQL Commands. First of all, we need to create a database and backup it.

-- Create an empty database
CREATE DATABASE SQLDatabase
GO
-- Select FULL RECOVERY model
ALTER DATABASE SQLDatabase SET RECOVERY FULL
GO
-- Use the database
USE SQLDatabase
GO
-- Create first table
CREATE TABLE MyDataTable1 (iCol INT)
-- Make Full backup
BACKUP DATABASE SQLDatabase TO DISK = 'D:SQLDatabase_Full1.bak' WITH FORMAT
-- Create second table
CREATE TABLE MyDataTable2 (a INT)
-- Make transaction log backup 
BACKUP LOG SQLDatabase TO DISK = 'D:SQLDatabase_Log1.trn' WITH FORMAT
-- Create third table
CREATE TABLE MyDataTable3 (a INT)
-- Make another transaction log backup
BACKUP LOG SQLDatabase TO DISK = 'D:SQLDatabase_Log2.trn' WITH FORMAT
-- Create forth table
CREATE TABLE MyDataTable4 (a INT)
-- Perform another full backup
BACKUP DATABASE SQLDatabase TO DISK = 'D:SQLDatabase_Full2.bak' WITH FORMAT

Here what we have done:

  • Create the first  Full backup  (SQLDatabase_Full1.bak)
  • Create the first Transaction log backup  (SQLDatabase_Log1.bak)
  • Create the second Transaction log backup (SQLDatabase_Log2.bak)
  • Create the second Full backup (SQLDatabase_Full2.bak)

Now let’s try to reproduce the error:

USE MASTER
GO
ALTER DATABASE SQLDatabase SET single_user WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE SQLDatabase
GO
RESTORE DATABASE SQLDatabase FROM DISK = 'D:SQLDatabase_Full1.bak' WITH NORECOVERY
GO
RESTORE LOG SQLDatabase FROM DISK = 'D:SQLDatabase_Log2.trn' WITH NORECOVERY
GO

Here we receive the following error message:

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

The reason we receive this error message is that we didn’t restore the second  transaction log backup.  SQL Server gives us the hint “An earlier log backup that includes … can be restored”. Now let’s take the list of backups taken from MSDB database using the following script:

DECLARE @db_name VARCHAR(100)
SELECT @db_name = 'SQLDatabase'
-- Get Backup History
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

Here what we have received:

The log in this backup set begins at LSN, which is too recent to apply to the database

All we need to do is to check all backups and find which one we have missed and then restore it.

[Total: 4    Average: 4.5/5]

Leave a Reply

Your email address will not be published.