SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restore with no full backup available


Restore with no full backup available

Author
Message
kwilt
kwilt
Old Hand
Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)

Group: General Forum Members
Points: 389 Visits: 394
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...
kwilt
kwilt
Old Hand
Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)

Group: General Forum Members
Points: 389 Visits: 394
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: General Forum Members
Points: 148077 Visits: 45868
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


kwilt
kwilt
Old Hand
Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)

Group: General Forum Members
Points: 389 Visits: 394
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: General Forum Members
Points: 148077 Visits: 45868
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


kwilt
kwilt
Old Hand
Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)

Group: General Forum Members
Points: 389 Visits: 394
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: General Forum Members
Points: 148077 Visits: 45868
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


kwilt
kwilt
Old Hand
Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)Old Hand (389 reputation)

Group: General Forum Members
Points: 389 Visits: 394
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search