March 22, 2011 at 7:33 pm
Ran into a strange problem today.. I'm trying to restore a differential backup of a mirrored database to a new database name. I restore the full backup and leave it in the restoring state. I then try to restore the differential and get the following error:
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Ok. That error sounds like it's telling me the differential is based on a different full backup than the one I restored. Except there has been no other full backup made. I checked the backup tables in msdb and verified this. I have:
full backup
diff1 backup
diff2 backup
diff3 backup
I'm want to restore to diff3, but I tried restoring diff1 and diff2 as well and they failed with the same message.
As I said, the database is mirrored, but I am restoring to a new database name, so I don't think mirroring plays any part in this. And I am able to restore only the full backup successfully.
All my backups are done via maintenance plans and I was able to restore a different database using full backups and the differential backups from the same days in question.
Any ideas?
March 22, 2011 at 11:52 pm
Yup, it's telling you that the diffs are based on a different full backup to the one you have.
Are all the backups taken natively? Do you have anything else backing up the DB?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 23, 2011 at 12:10 am
I have:
full backup
diff1 backup
diff2 backup
diff3 backup
I'm want to restore to diff3, but I tried restoring diff1 and diff2 as well and they failed with the same message.
you should be restoring latest full and then latest differential.
----------
Ashish
March 23, 2011 at 8:54 am
Gail - Yup - the backups are all done natively. Nothing else is backing up the databases. We don't use any third party tools.
Ashish - Yes, I tried restoring the full backup then diff3. That failed. Then tried full backup and diff2. That failed. Then full backup and diff1. That failed.
Just thought of something.. We have started using some software from our SAN vendor to make snapshot backups of the SAN volume. That might be causing issues. I'm thinking maybe the SAN snapshot happened while the diff backup or full backup was being created and maybe that messed something up. The different database I was able to restore successfully was much smaller, so it might have completed before the SAN snapshot. I vaguely remember reading something about SAN snapshots causing problems.. I'll have to look into that..
Shaun
March 23, 2011 at 8:57 am
That's why I was asking about other product backups. The SAN snapshot may count as a full backup and hence be the base for the diff (taken between the native full backup and the first diff)
Do a restore headeronly on the full and the first diff and check the LSNs, I can't remember exactly what columns appear, but you should be able to figure it out.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 23, 2011 at 9:28 am
Thanks. I'll try that. But I did check msdb backupset and one of those other msdb backup tables and it didn't show any other full backups being made. I'm assuming that if SQL thought a full backup was made, it would be logged there.
March 23, 2011 at 9:35 am
It should, but the error indicates that something reset the differential base. I have heard of this before with backupexec backups, can't recall details.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 23, 2011 at 10:32 am
Some of the SAN vendors will allow the option whether they want to write the snapshot backups to the history tables. Kind of them.... Others do it as a default, probably after much railing from DBA's.
Had this happen as well on a very large database that we were dependent on the full backup for. Painful.
The LSN's that you need should be in the backupset table in msdb. I don't have a query handy that will show the relation to full and differential LSN's but there might be something out there if you search the web for it.
A good review document to read. http://msdn.microsoft.com/en-us/library/ms190729.aspx
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 23, 2011 at 10:38 am
Figured it out and it was due to a combination of two problems.
We have a SAN backup that, for some reason, is causing mirroring to fail over to the mirror server. My diff and tlog backup jobs check for the existence of a full backup. If there isn't one, it makes one, then makes the diff or tlog backup.
What happened was this: The database failed over to the mirror server due to the SAN backup. Then the diff backup job on that server ran. It discovered there was no full backup of the database made on that server (it queries the msdb tables), so it made one. The next day, the admin came in and failed everything back to the original primary, where things normally run. So the base full backup was left stranded on the mirror server. This also explains why the msdb tables on the principle didn't show an additional full backup - because it was made on the mirror server.
So the restoring problem is solved, but we still have the issue of the SAN backup causing a mirroring failover. But that's a problem for another day.
Thanks for the help everyone!
Shaun
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply