Sometimes I Out-Smart Myself

Got a call from the company I used to work for and they needed some help with restoring a backup. No problem. Sounded easy. Turns out, it wasn’t quite so simple. In fact, I had to put on my robot hat again.

The company was trying to restore a database to a point in time. This involved restoring a full backup, then a differential backup. The full backup would restore but when they tired to restore the differential, they got this error:

This differential backup cannot be restored because the database has not been restored to the correct earlier state.

Hmm. First thing I did was try the restore myself. Sure enough, I got the same error. Ok. So the error is telling me that the differential I am trying to restore is based on a different full backup than the one I restored. Except, there was no other full backup made between the full backup I had and the differential. There were no other full backup files in the backup directory. I checked the backupset table in msdb. It only showed the full backup I had. Something strange was going on.

I used the following comands to look at the two backup files I had:

RESTORE HEADERONLY FROM DISK = '<path and filename of fullbackup>'

RESTORE HEADERONLY FROM DISK = '<path and filename of a differential backup>'

If the differential was based on the full backup I had, the number in the DifferentialBaseLsn column from the second command should be the same as the number in the FirstLsn column from the first command. They did not match. Clearly, there was another full backup somewhere. But nothing was logged in the msdb backup tables, so how was that possible?

Time to step back and look at the bigger picture. The database in question was a mirrored database. We were trying to restore it to a differently named database for some testing. I also discovered they had implemented some sort of SAN backup job that was taking snapshots of the SAN. This wasn’t working quite right and was causing the mirrored databases to fail over to the mirror server. But each day, the admin would fail everything back to the original principal server.

Could the SAN backup somehow be making a SQL backup without SQL logging it? Maybe. Then something my old co-worker said got me thinking. I’ve written about the backup jobs I use before. They are written such that if a differential or transaction log backup is needed and there is no full backup, it will make a full backup so the job won’t fail.

Here’s what I surmised was happening: The SAN backup causes the mirrored database to fail over to the other server – where this particular database is normally in the restoring state because it’s usually the mirror copy. On that server, the differential backup job runs. It discovers the failed over database, which is no longer in the restoring state but is now active, does not have a full backup made on that server (my job queries the msdb backup tables to determine this and since it is usually in the restoring state, it is never backed up, hence no backup log entry in msdb), so it makes one. Hours later, the admin comes in and fails the databases back to the other server. That means a full backup was made on what was now the mirror server and left there. Bingo! We checked the backup directories on that server and found a full backup. We used that and were able to restore with the differential we had. This also explains why the principle server had no record of the full backup being made in the msdb tables – it was actually made on the other server.

Leave a Reply

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

I am a real person and can prove it by doing this math problem: (required) Time limit is exhausted. Please reload CAPTCHA.

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