April 22, 2009 at 2:13 pm
We have a database that must be restored to a point in time from today's transaction log backups. The database was restored from a different databases' backup file on Monday, but no full backup was taken at that time (hence the problem.) A differential was taken on Monday and Tuesday nights, and transaction logs taken periodically today.
Our dilemma is that we need to restore to a point in time today, but due to the fact that no full backup is available as our starting point, when we try to apply the differential from last night, we receive an error that the wrong version of the differential cannot be applied.
Is there any way to restore a database when no full backup is available? I already know the answer but have been told to ask anyway...
April 22, 2009 at 2:21 pm
I need to revise this request a bit.
We do have a full backup from a database outside the backup set that represents our starting point (the database we're trying to restore was actually built from that backup file.)
Is there a way to script a backup job that restores the full backup from one database and applies a differential and three transaction logs to that?
I hope this makes sense, it's a bit of an odd situation.
April 23, 2009 at 1:48 am
Differentials are based on the previous full backup. If you do not have the last full backup that ran before the differential, you cannot use that differential backup.
If you have an unbroken chain of log backups since the full backup that you have, you can restore the full and then restore the logs in sequence.
If you do not have a full and unbroken log chain, then you cannot restore to the latest point.
Can you be more specific about what backups were taken and which ones you have available?
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
April 23, 2009 at 7:04 am
Thank you very much for responding.
Here are the sequence of events:
4/18 9:00 pm A full backup was taken of the Db
4/20 3:00 am The database was restored from a backup of our production database (a routine, daily process)
4/20 1:00 pm The data was refreshed with production data (and the routine restore job was disabled to allow a long-term test with a static Db)
4/20 2:00 pm A transaction log backup was taken
4/20 4:00 pm A transaction log backup was taken
4/20 9:00 pm A differential backup was taken
4/21 10:00 am - 4:00 pm Transaction logs taken ever 2 hours
4/21 9:00 pm A differential backup was taken
4/22 10:00 am - 2:00 Transaction logs were taken every 2 hours
4/22 3:00 pm - Users discovered a problem with one of their testing processes and requested a restore to 2:00 pm
We began the restore by backing up the tail of the transaction log. Database is now in a "restoring" state.
Then we began the restore through the GUI. The restore set offered by the GUI backup process included the following files:
4/18 9:00 pm Full Backup
4/21 9:00 pm Differential Backup
4/22 10:00 am - 2:00 pm Transaction Log Backups
It was at this time we received the error message indicating the differential backup was not the same version, and it dawned on us that the full backup we were using for the starting point was basically from an entirely different database and the integrity of our backup set was compromised.
At this point, if we can rebuild the "starting point" and apply all the transaction logs, I believe we have all the data.
Since we started the restore, the database is now in a "restoring" state, and it is my understanding that there is no way to cancel the restore and just return the database to the state it was prior to the restore. Can you confirm this? Also, are there any other options you can think of that we could try if we are unable to rebuild a full backup and restore over that with transaction logs? Any "ROLLBACK" options for example?
This is not a production issue, but it is a critical testing database utilized for a MTP with an extremely tight deadline. If we don't have to lose 3 days of testing, we would be very relieved, but the longer it takes to figure this out the fewer options we have.
Thanks for any suggestions you can give!
Kay
April 23, 2009 at 7:35 am
If you've already restored the full backup, you can't go back as the restore would have overwritten the existing database. You can just run the following to bring it out of the restoring state
RESTORE DATABASE <DBName> WITH RECOVERY
That should bring it online at whatever point in the restore it was.
What you can try, and I make no guarantees that it will work, is to start the restore process with that backup of production from 4/20, followed by the differential from 4/21, followed by all the tran log backups until after the time that you want to stop, restoring them with the NORECOVERY, STOPAT options.
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
April 23, 2009 at 9:47 am
Is it possible to apply a transaction log that is outside the regular backup set?
We are in the process of rebuilding our database to a "year end" state. If we can apply the transaction and differential backups to that database, we can get back to our point in time testing status. But in order to do that, we would have to apply those logs and differentials to a new database. I don't believe this is possible.
April 23, 2009 at 10:40 am
To restore to a point in time with log backups you need a full backup and all of the log backups (unbroken chain) from the full backup up until the time that you want to restore from. Any restore has to start with a full backup.
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
April 23, 2009 at 11:00 am
Well I think what we're trying to do is the impossible, and we'll have to simply restore the database to the beginning of the critical test. It was a good try, and we've learned a lesson about taking a full backup after a restore from another database. We've lost 2 full days of work, plus the day we spent trying to recover.
I thank you very much for all your advice and even though it was one of those painful lessons, learning it this way means we won't likely forget it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply