database restore chain

Question

database restore chain

sqlfriend

SSC Guru

Points: 52781
More actions
November 1, 2024 at 9:19 pm

#4476598

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

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

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

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

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

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

sqlfriend SSC Guru Points: 52781 More actions · Answer 3

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 2 weeks, 5 days ago by sqlfriend.
This reply was modified 2 weeks, 5 days ago by sqlfriend.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

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

sqlfriend SSC Guru Points: 52781 More actions · Answer 5

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.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

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 !