database restore chain

  • I have a backup of full, differential and transaction log setup for our database. Weekend full, daily differential, and every 3 hr transaction log backup.

    Now assume I have a request to restore a database to 2:00pm, but my most recent log backup is at 12:00pm, and the 3 pm transaction log backup has not been run yet. I know I can run a tail backup, then it will be included, and I can do a point of time restore to 2:00pm. But I accidently did a full backup not tail backup.

    Does that mean I no longer can restore to 2:00pm?

    Thanks

  • The full backup doesn't affect the transaction log - so you can just perform the 3pm log backup as normal and use that to restore to the 2pm point in time.

    What you should remember is that you can restore to a point in time from *any known good full backup* and an unbroken chain of log backups to that point in time.  For example, in your scenario - you attempt to restore from the latest full backup and find that backup file was corrupted.  You can restore the previous full backup and every log backup from that full backup to the point in time you want to recover.

    Differential backups are tied to the latest full backup - so if you have to revert to the full prior to the latest, you could restore that full - the differential backup prior to the latest full - and then log backups from there.  You could not restore from the prior full - and apply the latest differential, because that latest differential is based on a different full backup.

    To repeat - full backups do not affect transaction log backups.  Log backups can be applied given any full backup - as long as you have an unbroken chain of log backups from the full to the point in time to recover.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Thank you, got it.

    So the transaction log backups can be used with any full backup done earlier as long as the backup chain is not broken.

    Differential backup is based on most recent full backup.

    Thanks

  • And I was a little confused about this with oracle level 1 differential incremental backup, I think that one has to depend on the most recent level 0 or  level 1 backup, but not previous level 0.

    So both oracle differential incremental backup and accumulative incremental can only be used with most recent level 0, not previous level 0 I think

    • This reply was modified 14 minutes ago by  sqlfriend.
    • This reply was modified 12 minutes ago by  sqlfriend.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply