This differential backup cannot be restored


#1

I have sqlbackupandftp pro 2-license installed on two boxes, A and B. I have A set to do full back every week with differentials every day. That works and the backups are sent to a S3 bucket.

After the full back up runs. I go to machine B and run restore and the full backups are restored from the s3 bucket.
However, after a couple of days I go to machine B and run restore with the “last” option from the dropdown and then I always get this error.

[Error] Failed to restore database. One or more errors occurred. > This differential backup cannot be restored because the database has not been restored to the correct earlier state.
RESTORE DATABASE is terminating abnormally.

I’ve tried deleting old diffs and leaving just the full and one diff but it doesn’t matter. I still get that error listed above.

I then have to delete all the diffs leaving just the fulls for the restore to work.

How can I resolve this issue so my differentials restore without error?

Thank you.


#2

Hi Grave_Pushe,

It seems you get this issue because the backup chain is broken. Please find more details at https://sqlbak.com/academy/backup-chain

Also, you can find more details about the issue you have at https://sqlbak.com/blog/this-differential-backup-cannot-be-restored-because-the-database-has-not-been-restored-to-the-correct-earlier-state

Please let us know if you have any further questions.


#3

Hi Alexander -
Yes, I had read those articles. However, why would the chain be broken when sqlbackupandftp makes all the back up files and then I manually start the sqlbackupandftp restore on the other box? sqlbackupandftp is supposed to be smart and figure everything out so where is the breakdown happening?


#4

Hi Grave_Pushe,

Perhaps you perform backups via another tool or via another backup job or manually create other backups. All these can break the backup chain. SQLBackupAndFTP can restore these backups if you specified correct backup with the correct backup chain.

You can try to restore these backups manually via SSMS (SQL Server Management Studio) and you’ll get the same error message.

To avoid this issue you can try to backup your databases with the COPY_ONLY" option https://sqlbak.com/academy/copy_only_backup

Sorry for the inconvenience.