Diff versus Log

I'm confused. Let's say I want to be able to back up to
one hour ago on several critical databases, i.e. I'm
willing to accept a maxiumum of 1 hour of data loss. Is
it better to use Differential backups or Log Backups or
does it matter? What are some of the basic pros/cons?Differentials only look at the changes in the data. If you make UPDATES,
then this may work for you. If you do a lot of INSERTS and DELETEs, then it
may not.
A differential is good from the last time it was backed up.
A transaciton log allows you to restore to a point in time. So you could
recover up to 20 mins ago when something bad happened. The Diff backup
won't allow you to do this. You can apply the diff backup from 1 hour ago
and lose everything up to the last 20 mins. (Unless you are doing log
backups as well. Then simply backup the log and restore it to 20 minutes
ago.)
Diff recoveries are faster than TLog recoveries in that you only have to
apply the last diff backup. With logs, you restore your last good database
backup and then run all the logs in between.
Sooo.. It depends on what you are after.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?|||Hi Rick
I'm not sure what you're saying here. As far as a differential backup is
concerned, updates are the same as inserts or deletes. Any change to an
extent is backed up in the differential, whether that changes comes from
updating existing rows, or from adding or removing rows.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...
> Differentials only look at the changes in the data. If you make UPDATES,
> then this may work for you. If you do a lot of INSERTS and DELETEs, then
it
> may not.
> A differential is good from the last time it was backed up.
> A transaciton log allows you to restore to a point in time. So you could
> recover up to 20 mins ago when something bad happened. The Diff backup
> won't allow you to do this. You can apply the diff backup from 1 hour ago
> and lose everything up to the last 20 mins. (Unless you are doing log
> backups as well. Then simply backup the log and restore it to 20 minutes
> ago.)
>
> Diff recoveries are faster than TLog recoveries in that you only have to
> apply the last diff backup. With logs, you restore your last good
database
> backup and then run all the logs in between.
> Sooo.. It depends on what you are after.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "G2SL" <anonymous@.discussions.microsoft.com> wrote in message
> news:184301c48c72$89430e10$a601280a@.phx.gbl...
>|||So either solution should work for me since I've got a one
hour max?

>--Original Message--
>Hi Rick
>I'm not sure what you're saying here. As far as a
differential backup is
>concerned, updates are the same as inserts or deletes.
Any change to an
>extent is backed up in the differential, whether that
changes comes from
>updating existing rows, or from adding or removing rows.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>news:ea6nwkHjEHA.3968@.TK2MSFTNGP10.phx.gbl...
you make UPDATES,[vbcol=seagreen]
and DELETEs, then[vbcol=seagreen]
>it
up.[vbcol=seagreen]
time. So you could[vbcol=seagreen]
happened. The Diff backup[vbcol=seagreen]
backup from 1 hour ago[vbcol=seagreen]
you are doing log[vbcol=seagreen]
restore it to 20 minutes[vbcol=seagreen]
you only have to[vbcol=seagreen]
your last good[vbcol=seagreen]
>database
message[vbcol=seagreen]
to[vbcol=seagreen]
Is[vbcol=seagreen]
or[vbcol=seagreen]
>
>.
>|||I'm not sure what you mean by 'one hour max'. How often are you making backu
ps? If a backup spans more than one hour, but you want to restore to a certa
in point in time, you need log backups.
There are many questions that must be answered to come up with the best backup strategy
for you; amount of acceptable work loss is only one of the questions. Start by reading
everything you can in Books Online about backup and restore, and then take a look at "
Microsoft SQL Server 2000 High Availability" detail/-/0735619204/104-2349825-6773545" target="_blank">http://www.amazon.com/exec/obidos/t...2349825-6773545
The chapter on Backup and Restore was written by Kimberly Tripp and it is th
e best part of the book!
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message news:02f101c48c7e$357192a0$a40
1280a@.phx.gbl...[vbcol=seagreen]
> So either solution should work for me since I've got a one
> hour max?
>
> differential backup is
> Any change to an
> changes comes from
> you make UPDATES,
> and DELETEs, then
> up.
> time. So you could
> happened. The Diff backup
> backup from 1 hour ago
> you are doing log
> restore it to 20 minutes
> you only have to
> your last good
> message
> to
> Is
> or|||If using a trx log approach, you will need to restore all logs from the last
full backup to the point in time you want to restore, while for a
differential backup, you only need to restore that single differential
backup.
Depending on the amount of changes in the database, the time taken to
perform each type of backup may also differ widely i.e. a differential
backup has to always record all changes since the last full backup, while a
trx log backup only records the changes since the last trx log backup. This
in turn will also affect the size of the backups and time to backup.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?|||But the short answer is yes, either will work for you as long as you do the
backup every hour... I suspect however that the log backup would be
preferable ( depending on the frequency of the full database backup.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"G2SL" <anonymous@.discussions.microsoft.com> wrote in message
news:184301c48c72$89430e10$a601280a@.phx.gbl...
> I'm confused. Let's say I want to be able to back up to
> one hour ago on several critical databases, i.e. I'm
> willing to accept a maxiumum of 1 hour of data loss. Is
> it better to use Differential backups or Log Backups or
> does it matter? What are some of the basic pros/cons?