SQL Server Backup – Resolving ‘Log in Backup Set is Too Recent’ Error

backuprestoresql serverssmstransaction-log

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!

Best Answer

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 :

enter image description here

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.

Related Question