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 1 month, 1 week ago by  sqlfriend.
    • This reply was modified 1 month, 1 week ago by  sqlfriend.
  • Do not perform a Tail log backup if you just need a transaction log backup !!

    A tail-log backups only purpose is that you can restore the original database as it was on the target server right before you performed the intended restore.

    What's the difference with a regular log backup? The use of parameter Norecovery. It sets the database in restoring state after it completes !

    ref: Tail-log backups (SQL Server)

    Use it wise !

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Johan Bijnens wrote:

    Do not perform a Tail log backup if you just need a transaction log backup !!

    A tail-log backups only purpose is that you can restore the original database as it was on the target server right before you performed the intended restore.

    Thank you!  When you say the green above, the target server server you mean the same server that the original database on, correct? still not sure what is the purpose of tail backup.

     

  • Just to be clear: A tail log serves the target database at the target server of your restore.  ( so in case you need to restore that one back to its original state before you launched the restore(s) )

    In any other case, do not use a tail log backup, but a regular log backup ! ( so the database state is not affected )

    If you restore te database in the backup to a new database name, you just need the full backup, potentially the last diff backup ( if log backups need to much time ) and subsequent log backups.

    We're always talking with reference of the PIT you aim for !

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 7 posts - 1 through 6 (of 6 total)

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