Differential Restore Fail

We have all heard that backup is not complete until you test it by restoring it.  We also know that part of a DR plan involves the testing of those backups through restores.

I recently had the opportunity to run through a test scenario and ran into a substantial failure.  It was nearly catastrophic due to the size of the database, size of the backups and location of the DR site where we were testing the recovery.  Let’s just fast forward to all of the fun rather than bore you with all of the minutia.

I began the test with the restore of the full database backup.  This restore took in excess of 16 hours.  Once complete, the next phase was to restore the latest differential and then log backups from there.

Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

This is where the fun began.  I sat there pondering this for a bit.  How could this be the case?  I verified that the Full backup was only run once via the regular schedule.  I even ran the following to script to check.



This code returned only the one full backup that I knew and expected should be there from within the past few days.  So I tried that differential restore again.  I also tried a new differential just in case.  All of that was futile.  Then I looked back to my script and removed the following line



Tada – that revealed the problem.  Suddenly I had a new full backup appear for each day since the full backup that was taken through the regularly scheduled means.  Each new backup had the following nomenclature for the backup path.



And then it became apparent what was happening.  I had been excluding backups with paths following that type of naming convention due to CommVault in the past.  In this case I knew that CommVault was not a part of the picture.  I did know however that SAN snapshots were being taken on a nightly basis.

This was the aha moment – SAN snapshotting was causing a full backup to be registered in SQL Server.  And it makes perfect sense when looking at the backup history and sizes for each of the differentials that had been taken.  The differential backups were almost always the same size.  If you are running SAN Snapshots, you may want to reconsider your differential backup plan.

This script (just the revised version of the above) will help list the Full and Differential backups that have been taken.



6 thoughts on “Differential Restore Fail”

  1. If you’re using Veeam to back up a VM I think there’s a slight twist to this. These lines show up in your modified script but if memory serves correctly we’ve successfully restore a diff from a backup taken prior to the Veeam backup. This may be because we only use Veeam to back up the OS drive, not the data drives. It issues a quiesce on all the drives regardless which is why the “{blah}” backups still show up. I don’t have control over our Veeam backups so I can’t test now to make sure I’m remembering correctly but I set it up so I can check this weekend.

  2. Hi Jason,
    Just a quick question on your query – does this work for you on versions < 2008? I know it's checking the version to decide whether to include the compressed_backup_size column but if I run this on 2005 I still get an "Invalid column name" error.
    I have a very similar query but have always just commented out the compressed column when running < 2008.

    – James

    1. Well, I thought I had tested it and had it working. Apparently I missed something. I have tried a couple of rewrites and still coming up short. I will post when I have it fixed.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.