The log in the backup set is too recent to apply to the database

Question

After restoring a database with NORECOVERY, I then attempted to apply transaction logs. I got the message:

Restore failed for Server ... Additional Information: System.Data.SqlClient.SqlError: The log in this backup set begins at LSN 47154000037511200001, which is too recent to apply to the database. An earlier log backup that includes LSN 47154000027507600001 can be restored.

I've found several posting on this, but they seem to repeat the obvious and recommend using an earlier log backup. I'm confused.

What I do is make a full database backup at 8:10PM, Starting at 8:00AM the next day and every 20 minutes thereafter until 8:00PM I run the query "backup log HPRS to disk = '...'", which creates the transaction logs. The full-backup cycle then repeats 10 minutes later.

My understanding (which may be wrong) is that the full backup clears transactions and if a 'backup log to disk' were run immediately thereafter it would be empty.

The backup log run at 8:00AM is the first backup done after the full backup the previous evening. As far as I know, there are no "earlier log backups" than this.

So, what's up with this and how do I fix it?

Answer 1

Hi @OHPRS Admin ,

Thank you for reaching out and welcome to Microsoft Q&A.

For you error message:

Restore failed for Server ... Additional Information: System.Data.SqlClient.SqlError: The log in this backup set begins at LSN 47154000037511200001, which is too recent to apply to the database. An earlier log backup that includes LSN 47154000027507600001 can be restored.

It seems that you are not restoring the necessary log backups as they were created. You need to restore each after your full/diff in the correct order they were created. You are trying to apply a Log file that finish with LSN 4715400003751120000 into a DB restored with LSN 47154000027507600001, possible that you are not restoring transaction log backups in order. The LSN of the Full Backup (restored) is older than the log file that is trying to apply. So, the log file would not be possible to apply, being a change in sequence.

From this blog, I get this method to resolve the issue:

Please use Restore Gene- to automate DB restores. You can even use it using PowerShell to fully automate restores.

In addition, you can use the script in this article to check all backups and find which one we have missed, then restore it. Also, you can use the script in this blog to determine for restoring the available backups in order.

Best regards,

Lucy Chen

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications

Answer 2

I posted a comment, but not really visible without opening comments. Here's what I posted:

I think I may have found the problem. The Transaction log backups are supposed to end before 8PM which is 10 minutes before the full-backup is run. However, I found that the scheduler runs the trans log backup again at 8:20P and 8:40P, despite being set to stop at 8P. Therefore, it appears that the 8AM trans log backup is not the first one after the full backup.

I tested this theory just now and that's what happened. Unbeknownst to me, two more transaction log backups were run after the full database backup. So, the trans log backup at 8:00AM was not the first one after the full-backup. When I ran the 8:20P translog backup, it worked. I then applied the 8:40P and all the rest starting at 8:00A the next day. All worked! Everyone's insistance that I there must be earler translog backup got me to investigating this further. Thanks.

Answer 3

Your post is not that claer/precise.

All I can say, for a restore you have to restore latest full backup + all log backups in the right order.

Answer 4

My understanding (which may be wrong) is that the full backup clears transactions and if a 'backup log to disk' were run immediately thereafter it would be empty.

No it would not. And it would be really bad if it did. You should be able to restore a full backup which is a week old, and then apply all transaction log backups since then. And that should work, even if there have been other full backups.

Why you would do this? Elementary, my dear Watson. The database is corrupted, and the corruption is present in the more recent backups, so you start with the most recent clean backup.

As for the error message you are getting, it seems that there is an earlier log backup you are missing. Theoretically, the log backup at 20:00 completed later than the full backup that started ten minutes later. You can view the backups taken by looking in the table msdb..backupset.