The log in this backup set is too recent, but it's from before the last full backup!

Question

I'm trying to restore a full database backup plus transaction logs to the most recent transaction log. I've set up transaction logs to ship out every 15 minutes. This setup was performed via the SMSS GUI. The full database backup starts at 12 AM daily, and usually finishes before 2 AM.

First, I restore the full backup with NORECOVERY.:

USE [master]
RESTORE DATABASE [DBNAME] FROM  DISK = N'X:\path\to\DBNAME.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5
GO

This completes successfully.

Then, I grab the oldest transaction log and run the following:

RESTORE LOG DBNAME FROM DISK = 'X:\path\to\OldestTransactionLog.trn' WITH NORECOVERY;

However, I get the following error:

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

I don't understand why this is too recent!

My backup takes place at 12 AM and finishes by 2 AM.

At first I tried the transaction log from 12 AM, then seeing the "too recent" message, tried the oldest one I have, from 11:30 AM yesterday.

So this transaction log should be half a day older than the most recent full database backup.

Am I missing something here? How can this be too recent?

Any help is appreciated - and this is not on a production system, don't worry!

Answer 1

The last LSN of the FULL BACKUP should match the Last LSN of the log backup. This will be your first log backup and then after that the consecutive log backups will be having A.LAST_LSN = B.FIRST_LSN --> where B = log backup taken immediately after A.

From BOL :

Now look at the error you are getting :

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

So, essentially, you have a gap in the LSN and hence you cannot restore your log backup.

I would suggest to use Restore Gene - to automate DB restores. You can even use it using PowerShell to fully automate your restores. (I have used it and its very useful.)

Refer to my answer to better understand how full backup and LSN works.

Answer 2

You are trying to apply a Log file that finish with LSN 421814812000000025600001 into a DB restored with LSN 421787067000000013800001 , so, the LSN of the Full Backup (restored) is older than the log file that is trying to apply. So, the log file would not possible to apply , being a change in sequence.

So possible cause here being that you are not restoring transaction log backups in order :

Also, you can use the below script to determine for restoring the available backups in order:as mentioned here

    USE Master; 
GO  
SET NOCOUNT ON 

-- 1 - Variable declaration 
DECLARE @dbName sysname 
DECLARE @backupPath NVARCHAR(500) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fileList TABLE (backupFile NVARCHAR(255)) 
DECLARE @lastFullBackup NVARCHAR(500) 
DECLARE @lastDiffBackup NVARCHAR(500) 
DECLARE @backupFile NVARCHAR(500) 

-- 2 - Initialize variables 
SET @dbName = 'Customer' 
SET @backupPath = 'D:\SQLBackups\' 

-- 3 - get list of files 
SET @cmd = 'DIR /b ' + @backupPath 

INSERT INTO @fileList(backupFile) 
EXEC master.sys.xp_cmdshell @cmd 

-- 4 - Find latest full backup 
SELECT @lastFullBackup = MAX(backupFile)  
FROM @fileList  
WHERE backupFile LIKE '%.BAK'  
   AND backupFile LIKE @dbName + '%' 

SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''  
       + @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE' 
PRINT @cmd 

-- 4 - Find latest diff backup 
SELECT @lastDiffBackup = MAX(backupFile)  
FROM @fileList  
WHERE backupFile LIKE '%.DIF'  
   AND backupFile LIKE @dbName + '%' 
   AND backupFile > @lastFullBackup 

-- check to make sure there is a diff backup 
IF @lastDiffBackup IS NOT NULL 
BEGIN 
   SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK = '''  
       + @backupPath + @lastDiffBackup + ''' WITH NORECOVERY' 
   PRINT @cmd 
   SET @lastFullBackup = @lastDiffBackup 
END 

-- 5 - check for log backups 
DECLARE backupFiles CURSOR FOR  
   SELECT backupFile  
   FROM @fileList 
   WHERE backupFile LIKE '%.TRN'  
   AND backupFile LIKE @dbName + '%' 
   AND backupFile > @lastFullBackup 

OPEN backupFiles  

-- Loop through all the files for the database  
FETCH NEXT FROM backupFiles INTO @backupFile  

WHILE @@FETCH_STATUS = 0  
BEGIN  
   SET @cmd = 'RESTORE LOG ' + @dbName + ' FROM DISK = '''  
       + @backupPath + @backupFile + ''' WITH NORECOVERY' 
   PRINT @cmd 
   FETCH NEXT FROM backupFiles INTO @backupFile  
END 

CLOSE backupFiles  
DEALLOCATE backupFiles  

-- 6 - put database in a useable state 
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' WITH RECOVERY' 
PRINT @cmd 

Answer 3

When trying to restore from SMSS I found that it skipped my first t-log. I modified the statement to include the missing transaction and had no issue. Maybe this occurs due to overlapping execution of full backup and hourly log (uneducated guess)?