Log Backup Frequency and Max Data Loss

Question

Log Backup Frequency and Max Data Loss

SteveBell

SSCrazy

Points: 2008
More actions
September 14, 2010 at 11:00 am

#229290

Hello all,
I've just read a post by one of the many prolific and helpful responders in this forum.
It related to a question by a poster who asked how often to backup transaction logs. There was excellent advice, tips and perspectives to consider.
I believe that I'm getting a solid (beginner's) understanding of the full recovery model and transaction log management.
However, I'm trying to get my head around one post said "The interval between log backups should be the maximum allowable data loss for the database."
I thought that the full recovery model, properly managed, would eliminate data loss?
PS: After reading these various SSC forums for some weeks now, I want to thank the many people who put a great deal of effort into helping others.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Depends.

If you're doing log backups every 15 minutes and immediately archiving them to another data centre and the data file is fatally corrupted 1 minute before the next log backup, you'll be able to do a tail-log backup, taking the transactions up to the second of failure and then recover without any data loss.

If you're doing log backups every 15 minutes and immediately archiving them to another data centre and a meteor hits the server room a minute before the next log backup, then you will have lost 14 minutes of data as there's no way to get at the transaction log to take that tail log backup.

If the disaster has left SQL running and the log file available (regardless of the data file) you can restore right up to the point of failure. If the disaster has destroyed the log file (drive failure, log corruption) then you've lost anything past that last log 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

SteveBell SSCrazy Points: 2008 More actions · Answer 2

Thanks Gail,

You're one of the people I was thinking of in the postscript to my original post.

So yes, barring a meteor impact or extraordinary chain of events, we can recover to the point of failure.

Thank you again,

Steve

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

SwayneBell (9/14/2010)
So yes, barring a meteor impact or extraordinary chain of events, we can recover to the point of failure

No, not at all.

All that you need is for the disaster to damage the log file (as well as the data file) so that a tail-log backup is not possible, eg Log corruption, log drive failure (multiple drives if it's RAID) or for something to prevent SQL from running (can't take a tail-log backup if SQL's not running) eg OS drive failure, server failure, total SAN failure (saw that just a month ago)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

SwayneBell (9/14/2010)
Thanks Gail,
You're one of the people I was thinking of in the postscript to my original post.

p.s. Thank you.

I thought you were talking about me in the main post, as that's exactly the phrasing I use.

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

SteveBell SSCrazy Points: 2008 More actions · Answer 5

GilaMonster (9/14/2010)
SwayneBell (9/14/2010)
Thanks Gail,
You're one of the people I was thinking of in the postscript to my original post.
p.s. Thank you.
I thought you were talking about me in the main post, as that's exactly the phrasing I use.

Yes, it is the phrasing you use and yes I was quoting you.

And PPS: You're quite welcome. You help a lot of people here.

SteveBell SSCrazy Points: 2008 More actions · Answer 6

GilaMonster (9/14/2010)
SwayneBell (9/14/2010)
So yes, barring a meteor impact or extraordinary chain of events, we can recover to the point of failure
No, not at all.
All that you need is for the disaster to damage the log file (as well as the data file) so that a tail-log backup is not possible, eg Log corruption, log drive failure (multiple drives if it's RAID) or for something to prevent SQL from running (can't take a tail-log backup if SQL's not running) eg OS drive failure, server failure, total SAN failure (saw that just a month ago)

Thanks for the qualification. So, it may be more likely to happen than we'd like. When I originally read the post that I quoted in my OP, I assumed that the guideline had something to do with not being able to get at the tail-log. This is very helpful once again.

Steve

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

SwayneBell (9/14/2010)
When I originally read the post that I quoted in my OP, I assumed that the guideline had something to do with not being able to get at the tail-log.

It does. You can't take a tail-log backup if (1) the log file is not available (2) SQL is not running. No tail-log backup, not recovery to point of failure.

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