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:
- Full nightly backup that runs at 6 PM.
- Hourly differential backup during the day.
- 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?
Best Answer
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.