Differential Restore Fail

Categories: Corner, News, Professional, SSC, SSSOLV
Comments: 8 Comments
Published on: November 28, 2012

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.

 

 

8 Comments - Leave a comment
  1. Chris F says:

    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. James Lean says:

    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

    • Jason Brimhall says:

      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.

  3. quoted on Backup History | SQL RNNR says:

    […] can see the old script here, shown as a part of investigating why differential backups were failing to restore. Code […]

  4. […] backups as a general matter.  Earlier, Jason Brimhall talked about an experience he had where he wasn’t able to restore a differential due to a SAN snapshot and provided a script to see where this situation occurred.  In his case he was testing his […]

  5. […] accordingly so the business can recover appropriately in the event of a disaster. Related Posts:Differential Restore FailBackup HistoryDatabase In RecoveryDatabase Data and Log Size InfoSQL Confessions 02 SSRS […]

Leave a comment

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">








Calendar
November 2012
M T W T F S S
« Oct   Dec »
 1234
567891011
12131415161718
19202122232425
2627282930  
Content
SQLHelp

SQLHelp


Welcome , today is Sunday, December 21, 2014