1

I'm getting the following error as I'm trying to restore from a differential backup:

System.Data.SqlClient.SqlError: This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time.

We are using SQL Server 2008 R2 with 2 small databases (A:Production, B:Testing), in full recovery mode.

My daily backups are:

  1. Full nightly backup that runs at 6 PM.
  2. Hourly differential backup during the day.
  3. Log backups every 30 minutes during business hours.

I usually restore my testing database from the latest full backup using Management Studio, but today we discovered that some of the reports are not working correctly, so I'm trying to check what happened exactly yesterday during business hours so I tried the differential backup for the first time.

I got the following error:

the log or differential backup cannot be restored because no files are ready to roll forward

Then I manually backed up the Testing database, restored it using this same manual full backup (since the one from last night's production is not working), with Replace and NoRecovery options, then I tried restoring the differential from yesterday morning, but I get the following error about paths:

This backup set cannot be applied because it is on a recovery path that is inconsistent with the database...

Any advice on what am I doing wrong or how can I keep restoring from all the differentials until the end of the day?

Improve this question
5
  • 2
    Show us your commands as well. Restore latest full, then only the latest diff and then subsequent log backups taken after the diff. To help generate scripts, search for restore genie from Paul bewer on sqlservercentral.com
    – Kin Shah
    Commented Sep 10, 2015 at 23:07
  • I'm not sure that you want to fix "reports not working correctly" by restoring an older copy of the database. Aren't there other troubleshooting steps you can take first? Like analyzing what "not working correctly" means, and see if there's a reason that doesn't mean the database must be corrupt or otherwise unusable in its current form?
    – Aaron Bertrand
    Commented Sep 10, 2015 at 23:47
  • @Kin I usually just use the Gui- SSMS for backup and restore as I'm not that good with TSQL yet, that's why I couldn't share any command. I restored my testing database from the latest production full backup for the prior night on (9/8th 6pm where there were no issues), then the last differntial backup (9/9th at 5:30pm), chose these options from the GUI: Restore ...WITH REPLACE, and Leave database ... with NORECOVERY so I can restore a subsequent log backup but I got an error of: "This diffrential backup cannot be restored because this db has not been restored to the correct earlier state".
    – Shayma Ahmad
    Commented Sep 11, 2015 at 3:25
  • @AaronBertrand One developer mentioned that they've modified something to a couple of stored procedures (for the reports in question above) on 9/9th and the reports can't be executed now (run by staff through a VB front end application) so that developer undo everything back to the original code today (9/10th) but still no luck! So I thought restoring a testing database back to yesterday (9/9th where corruption may happened? may help me trace what else was changed.. This might not be the best troubleshooting steps as I dont know how to trace back, and now we have more data added today too.
    – Shayma Ahmad
    Commented Sep 11, 2015 at 3:36
  • Still, "can't be executed" seems like something you can troubleshoot without blowing away all of the data since the change. Is nobody able to capture a decent error message, perhaps by executing the stored procedure outside the context of the application? I may be missing something here, but this is kind of like replacing the engine every time you run out of gas...
    – Aaron Bertrand
    Commented Sep 11, 2015 at 4:34

2 Answers 2

Reset to default
3

Here's what I have understood from your question :

Your Full backup taken yesterday failed to restore on your TestingDB. Hence, you took a latest full backup and that got restored successfully. After this you are trying to restore a differential backup from yesterday.

This will not work. Here's the reason it will not work. Everytime a backup happens let it be FULL\DIFF or LOG. it generates an LSN, this LSN is a chain and unique number which matches with each other and are compared at the time of restore.

If you have taken latest FULL backup then now you should take the latest diff backup and try to restore it on TestingDB and you should not get any errors you received earlier. Also, while restoring you can check the LSN of Full and Diff backup files by using RESTOREHEADERONLY command which reads the headers of your .bak file and gives you the information about First and Previous LSN.

I hope this helps in solve your problem.

Improve this answer
1
  • Thank you all for your comments. I was able to successfully restore from the Full, Differential and the Log backup file that I needed. The trick was to choose the "Restore with NoRecovery" option to leave the DB in a restoring mode until the last backup where I leave the database ready for use (with Recovery option). Plus following the right sequence of choosing the "Last" Full, the "Last" Differential and not cherry picking!
    – Shayma Ahmad
    Commented Sep 14, 2015 at 20:57
2

The answer is in your methods. First you were trying to restore a differential over a database that is in a more recent state. You would need to restore your last full backup first (from Production) and then your differential. The second time you were trying to restore a differential on a database that the backup was not taken from. SQL in both these cases will not let you restore the differential as the only thing it would lead to is corruption.

Improve this answer
1
  • correct, once I realized that I'm restoring over a DB that is in a more recent state, I manually restored it from a previous night production full backup where I know there were no issues, then tried to pick one the production's differentials for the following day to pinpoint what hour the errors starts, then I realized your 2nd comment, so I backed up manually the Testing DB that is in the working state still, restored it again from this TestingFullbackup... same thing, I'm confusing all! I may need to ready about the exact sequence of the backup and restore again.
    – Shayma Ahmad
    Commented Sep 11, 2015 at 3:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.