Log Backups vs. Differential Backups

I was recently educated (thanks Dan) that you cannot create a backup of
a database if you lose your log file (due to drive failure of
corruption). Once I learned this, I began wondering about the merits of
even using a log file with the Full recovery model. Why not just use
the Simple recovery model and use frequent differential backups? Why go
through the overhead of log files if you can get almost the same level
of safety using frequent differntial backups?
Is doing differential backups every 15 minutes close enough to the
level of protection afforded by 15 minute log backups?
The way I see it is that the only thing I lose is
up-to-the-point-of-failure restoration. But, what I gain is not having
to worry about a corrupt log file or losing the drive that has the log
file on it. The processing time seems similar.
Am I wildly wrong anout this?
Thanks in advance for your opinions.
- Sean
Hi Sean,
Are you say that you would only lose you log file drive? What happens if
it's the data drive that goes? If you lose your log file of the database
and then detach and reattach the drive the systm will create a new log file.
Then applying the log backups will get you back to the point of failure -
the time to the last log file backup.
Backups are also used for non disaster situations and log file give you more
options there. I have always found log file backups quicker that diff
backups when it
comes to large databases
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegro ups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>
|||Greg - thanks for your reply. I was not able to rebuild a log file. I
took a sample database with a log file, stopped SQL server, deleted the
log file, and restarted SQL server. The DB was "Suspect". I was unable
to get a new log to build with any consistency - I played with setting
the DB to "Emergency Mode" and messed with detaching/reattaching and
would get it to rebuild a log file 50% of the time - still, the problem
is by losing your log file, the data becomes suspect, and you can't
trust it even if you rebuild the log.
I understand how log file backups can get you to the point of failure
if the data drive fails - but I was concerned with the log drive
failing. This led me to post on the topic: "Log Backups - Why bother
with every ten minutes?" There I learned from Dan that if you lose a
log file, your database is hosed, and you can only restore to your last
backup.
This was a disappointment to me - I really like the idea that we can
restore to the point of failure using logs if the data drive fails -
but if the log drive fails, we are right back to only getting back what
we have as a "backup".
So - I am thinking that differentials in "Simple" recovery are pretty
close to as good as log file backups in "Full" Recovery mode. Granted,
you can't restore to the point of failure EVER - but you aren't that
much worse off if the backups are frequent enough.
You mention that log file backups are quicker than differentials. A
little quicker? or significantly quicker? The database sizes I am
concerned with are ~35 Gig. The full backup process to disk takes less
than 10 minutes. Diffs and log backups are much faster, naturally.
Are there other problems with diffs that I am unaware of - locking or
something?
|||Hi Sean,
In the situation you have describe to create a suspect database I was not
able to reproduct the issue. Having said that it doesn't really matter.
Any backup system that you employ you should follow the MS recommendation .
They are that transaction logs and Data should be protected while on disk.
That is with a form of RAID. While it is unlikely that a disk goes bad is
is very unlikely that two or more will go bad at the same time. So your
first line or protection is always a RAID system.
Next a diff backup is slower than a log backup. This is the bit from BOL
Note If you have created any file backups since the last full database
backup, those files will be scanned by Microsoft SQL ServerT 2000 at the
beginning of a differential database backup. This may cause some degradation
of performance in the differential database backup. For more information,
see Using File Backups.
So to do a diff backup it needs to read the backup an check the pages that
have been backed up. Where as a log backup backups the entire log file
(pages). Somethime the LOG backup can be larger than the database bacup
because of high transactions but you can always reduce the time between
backups.
In addition having log backups and transaction logs (Full Recovery Mode)
always the use of log reader type productions to see and recover cahnges to
the data without restoring. This might not be an issue now but at sometime
we have all been asked to get back some details that were changed wrongly.
Backup don't tend to cause locking problems for long periods as they backup
pages and do so quickly.
My recommendation is to continue with log backups , ensure that the data and
log files are on RAID systems (RAID 1 for log, RAID 5 for data is typically
good)
Send your backups and log backups to another server via a script See this
link http://www.sql-scripts.com/members/S...ls.aspx?S_ID=4
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124760340.712510.321430@.g47g2000cwa.googlegr oups.com...
> Greg - thanks for your reply. I was not able to rebuild a log file. I
> took a sample database with a log file, stopped SQL server, deleted the
> log file, and restarted SQL server. The DB was "Suspect". I was unable
> to get a new log to build with any consistency - I played with setting
> the DB to "Emergency Mode" and messed with detaching/reattaching and
> would get it to rebuild a log file 50% of the time - still, the problem
> is by losing your log file, the data becomes suspect, and you can't
> trust it even if you rebuild the log.
> I understand how log file backups can get you to the point of failure
> if the data drive fails - but I was concerned with the log drive
> failing. This led me to post on the topic: "Log Backups - Why bother
> with every ten minutes?" There I learned from Dan that if you lose a
> log file, your database is hosed, and you can only restore to your last
> backup.
> This was a disappointment to me - I really like the idea that we can
> restore to the point of failure using logs if the data drive fails -
> but if the log drive fails, we are right back to only getting back what
> we have as a "backup".
> So - I am thinking that differentials in "Simple" recovery are pretty
> close to as good as log file backups in "Full" Recovery mode. Granted,
> you can't restore to the point of failure EVER - but you aren't that
> much worse off if the backups are frequent enough.
> You mention that log file backups are quicker than differentials. A
> little quicker? or significantly quicker? The database sizes I am
> concerned with are ~35 Gig. The full backup process to disk takes less
> than 10 minutes. Diffs and log backups are much faster, naturally.
> Are there other problems with diffs that I am unaware of - locking or
> something?
>
|||Point in time restore can be vary valuable. Another thing that log backups allow you to do is to
backup the log after the data file is lost (or corrupted). Read about the NO_TRUNCATE option to the
BACKUP LOG command. I think you should ask yourself: "What advantages does db and diff backups have
in contrast to db and log backups?". I always go for log backups and only *complement* with diff
backups when needed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegro ups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>
|||I think Tibor's approach is a good one - use a combination of differential
database backups as well as log backups. This will simplify your recovery
procedure and still allow you to recover to the point of failure if you lose
data files.
Hope this helps.
Dan Guzman
SQL Server MVP
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124756827.011771.28120@.g44g2000cwa.googlegro ups.com...
>I was recently educated (thanks Dan) that you cannot create a backup of
> a database if you lose your log file (due to drive failure of
> corruption). Once I learned this, I began wondering about the merits of
> even using a log file with the Full recovery model. Why not just use
> the Simple recovery model and use frequent differential backups? Why go
> through the overhead of log files if you can get almost the same level
> of safety using frequent differntial backups?
> Is doing differential backups every 15 minutes close enough to the
> level of protection afforded by 15 minute log backups?
> The way I see it is that the only thing I lose is
> up-to-the-point-of-failure restoration. But, what I gain is not having
> to worry about a corrupt log file or losing the drive that has the log
> file on it. The processing time seems similar.
> Am I wildly wrong anout this?
> Thanks in advance for your opinions.
> - Sean
>
|||Thanks to all.
Greg - knowing that the differential backups have to read the full
backup was a key piece of information in understanding why it is slower
than Log Backups. I'll look into this more thoroughly.
Tibor/Dan - I understand that using all three (full backups,
differentials and Log backups) is the recommended practice - and I
understand how it helps you recover to the point of failure.
What still disappoints me is the fact that losing the log drive makes
you lose the database. It undermines my desire to go through the setup
hassle and expense of keeping and maintaing a transaction log. I have
to setup clients with our products database, and train them to do
backups. none of them are DBAs - many are at the level of novice. The
log backups invariably cause problems, and the drawbacks seem to
outweigh the benefits in these cases.
For myself, in a large installation - I would certainly keep a log and
do log backups - but I'm not sure I can gleefully recommend the same
practice to my clients.
Thanks again for the discussion - I appreciate it.
- Sean