Log Backups - Why bother with every ten minutes?

Most of my clients employ some reasonable RAID setups - mirroring at
least. Let's say this is the minimum configuration:
C: - OS
D: Data drives - (2 Mirrored drives - seperate controller)
E: Log Drives - (2 Mirrored drives - seperate controller)
Assume full backups happen every night at midnight, and take 15
minutes.
Why would I make transaction log backups any more frequently than
nightly? There are many respectable people that recommend backups of
transaction logs hourly, or even every ten minutes. Why?
The way I see it is if both the data drives fail, we can still do a log
backup after the failure and perform a complete recovery using last
nights backups. If both log drives fail, we simply get a new backup,
and move the log file until we replace the log drives.
If we backup the log every ten minutes, we introduce a huge recovery
headache by needing to restore evey log backup until the point of
failure - why would I want to do that?
Thanks in advance for your opinions.
- SeanThe worse-case scenario is that you lose your log. Since you cannot backup
the database without the log, your only recourse is to restore from your
last good database backup and then apply log backups. The frequency of your
log backups depends on how much data loss is acceptable in the unlikely
event both log drives are lost.
Hope this helps.
Dan Guzman
SQL Server MVP
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124673132.337738.144600@.g43g2000cwa.googlegroups.com...
> Most of my clients employ some reasonable RAID setups - mirroring at
> least. Let's say this is the minimum configuration:
> C: - OS
> D: Data drives - (2 Mirrored drives - seperate controller)
> E: Log Drives - (2 Mirrored drives - seperate controller)
> Assume full backups happen every night at midnight, and take 15
> minutes.
> Why would I make transaction log backups any more frequently than
> nightly? There are many respectable people that recommend backups of
> transaction logs hourly, or even every ten minutes. Why?
> The way I see it is if both the data drives fail, we can still do a log
> backup after the failure and perform a complete recovery using last
> nights backups. If both log drives fail, we simply get a new backup,
> and move the log file until we replace the log drives.
> If we backup the log every ten minutes, we introduce a huge recovery
> headache by needing to restore evey log backup until the point of
> failure - why would I want to do that?
> Thanks in advance for your opinions.
> - Sean
>|||Thanks Dan - so you are saying the SQL Server will not allow you to get
a full backup if the log drives fail? Wow. I didn't know that.
Even so - if the log drives fail - we haven't actually lost data yet,
right? Can't we add a new log file to keep going? Can we alter the
database and set it to Simple recovery to get a backup?
- Sean|||> Even so - if the log drives fail - we haven't actually lost data yet,
> right?
Data modifications are written to the log before the data files. The log
buffer is flushed during commit and this is what guarantees that your data
changes are permanent.

> Can't we add a new log file to keep going? Can we alter the
> database and set it to Simple recovery to get a backup?
Even if you could just add a new log, it would be a bad idea to do so. When
you lose your log, committed data may be lost and both logical and physical
database integrity would be at best questionable. Processing against a
potentially corrupt database can make matters worse. A database backup is
pretty much useless without the log.
There are undocumented and unsupported techniques to rebuild the transaction
log. This is kind of thing is done should be done as last resort (i.e. no
database backup available) and under the direction of Microsoft PSS. Of
course, you'll never find yourself in this nasty situation as long as you
have a sound recovery strategy.
Hope this helps.
Dan Guzman
SQL Server MVP
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124677496.824240.113320@.g14g2000cwa.googlegroups.com...
> Thanks Dan - so you are saying the SQL Server will not allow you to get
> a full backup if the log drives fail? Wow. I didn't know that.
> Even so - if the log drives fail - we haven't actually lost data yet,
> right? Can't we add a new log file to keep going? Can we alter the
> database and set it to Simple recovery to get a backup?
> - Sean
>|||On 21 Aug 2005 18:12:12 -0700, SeanNerd wrote:
(snip)
>Why would I make transaction log backups any more frequently than
>nightly?
Hi Sean,
Because the log file keeps growing until you take a log backup. After
that, space will be reused. Depending on the volatility of your data,
this might be irrelevant, or it might cause your log file to grow to a
multiple of the size of the data file.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)