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[vbcol=seagreen]
>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
>
>.
>
|||I'm not sure what you mean by 'one hour max'. How often are you making backups? If a backup spans more than one hour, but you want to restore to a certain 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" http://www.amazon.com/exec/obidos/tg...349825-6773545
The chapter on Backup and Restore was written by Kimberly Tripp and it is the 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$a401280a@.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?
sql

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?

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...
> > 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?
>|||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...
>> 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?
>>
>
>.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0D00_01C48C67.9ED536F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
I'm not sure what you mean by 'one hour max'. How often are you making =backups? If a backup spans more than one hour, but you want to restore =to a certain 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" =http://www.amazon.com/exec/obidos/tg/detail/-/0735619204/104-2349825-6773=
545
The chapter on Backup and Restore was written by Kimberly Tripp and it =is the 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$a401280a@.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...
> >> 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?
> >>
> >>
> >
> >
> >.
> >
--=_NextPart_000_0D00_01C48C67.9ED536F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

I'm not sure what you mean by 'one hour =max'. How often are you making backups? If a backup spans more than one hour, but =you want to restore to a certain 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" http://www.amazon.com/exec/obidos/tg/detail/-/0735619204/104=-2349825-6773545
The chapter on Backup and Restore was =written by Kimberly Tripp and it is the best part of the book!
-- =HTH--Kalen DelaneySQL Server MVPwww.SolidQualityLearning.com
"G2SL" wrote in message news:02f101c48c7e$357192a0$a401280a@.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" 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" 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?> =>>> >>> >> >> >.> >

---

--=_NextPart_000_0D00_01C48C67.9ED536F0--|||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?

diagram object behavior in EM

Dear Collective Wisdom,
Noticed something odd back when we first upgraded to XP in that when one
goes to view an existing relational diagram object for a database in
Enterprise Manager, everything appears blank in the diagram. New
diagrams, OTOH, can be created and viewed without any problem. Any ideas?
Thanks in advance!
gms--
Maybe this link will help you:
http://www.yiapap.com/bldiagra.htm
Cristian Lefter, SQL Server MVP
"Greg M. Silverman" <gmsRE@.MOVEumn.MEeduAT.COM> wrote in message
news:d48i5q$1m8$1@.lenny.tc.umn.edu...
> Dear Collective Wisdom,
> Noticed something odd back when we first upgraded to XP in that when one
> goes to view an existing relational diagram object for a database in
> Enterprise Manager, everything appears blank in the diagram. New
> diagrams, OTOH, can be created and viewed without any problem. Any ideas?
>
> Thanks in advance!
>
> gms--
|||Cristian Lefter wrote:
> Maybe this link will help you:
> http://www.yiapap.com/bldiagra.htm
> Cristian Lefter, SQL Server MVP
Thanks for the link... I did find this also info in the depths of
Technet, have yet to try it.
gms--

> "Greg M. Silverman" <gmsRE@.MOVEumn.MEeduAT.COM> wrote in message
> news:d48i5q$1m8$1@.lenny.tc.umn.edu...
>
>
|||Greg M. Silverman wrote:
> Cristian Lefter wrote:
>
> Thanks for the link... I did find this also info in the depths of
> Technet, have yet to try it.
>
> gms--
>
Ok, just did it and it worked!
Thanks!
gms--

Developing app that will use SQL Server Express on back end...

Hi...

I have the full version of SQL Server installed on my machine and am developing an app using BI DTS packages, VB.Net, SQL SPs, etc.. I will need to deploy this app w/SQL Server Express (SSE).

-Do I have to install SSE on my development machine for the purpose of creating the install package of my application? I am afraid that if I do install SSE on my machine, it may mess up my installation of the full version of SQL Server... will it?

-Can you point me to a link with information about how to do the development with the full version of SS, and create the installation package using the SSE?

Thanks for your help.

Steph
You cannot deployment SQL Server 2005 Integration Services packages in SQL Server Express because the runtime now comes with the server license. In SQL Server 2000 the DTS package comes with the runtime that changed in 2005. Hope this helps.|||Thanks for the response.

The way I posed my question was misleading. I'm sorry about that. I shouldn't even have mentioned the DTS packages.

The big picture of the app is this:

- Data resides in a datamart at a central office. The central office will be running the full SQL Server 2005 version (this is where the DTS packages reside and run).

- Satellite (very small) offices need to download updated information relating to them from the central office. I'm thinking of having these satellite offices run SQL Express.

I'm developing the code to update the satellite office data from the central office data. On my development system, I am running the full version of SQL Server 2005. Should I also install SQL Express on my development system ? Will it mess up my full SQL Server 2005 installation?

Thanks,

Steph
|||It is tricky but here is how you could do it legally create Asp.net application run it from your main office SQL Server create a job to execute the many versions of the package make your small offices destination. It will take more work but the alternative solution may cost you more hardware SQL Server Express can be a subscriber in replication so you can push the data with replication. You could always talk to your local Microsoft SQL Server contact person, in some areas there is regional contact person. Hope this helps

developed in 2005, need to go back to 2000

I've developed a web application and to be honest I've been a bit dumb and not checked with my hosts. I assumed they used SQL Server 2005. But no, they only use 2000.

I'm sure I'll come across more problems but the first I'm having is I've used the ROW_NUMBER() function in a few stored procedures, but in 2000 I'm getting this error message

'ROW_NUMBER' is not a recognized function name.

Here is the stored procedure for reference

SELECT RowNumFROM (SELECT id, category, ROW_NUMBER() OVER(ORDER BY id) as RowNum FROM custrecords ) as CustRecord

anyone know the SQL Server 2000 equivalent?

Regrettably ROW_NUMBER is a new feature with SQL2005.

What precisely are you trying to achive with your SELECT statement?

|||

there isn't a equivalent of row_number() in SQL Server 2000. Depending on your requirement, you can use a subquery something like

select count(*) from tbl where <condition>

to do it.

Maybe you can explain what are you trying to do here. Or alternatively do the row numbering in your ASP.NET

|||

khtan:

there isn't a equivalent of row_number() in SQL Server 2000. Depending on your requirement, you can use a subquery something like

select count(*) from tbl where <condition>?

to do it.

Maybe you can explain what are you trying to do here. Or alternatively do the row numbering in your ASP.NET
?

thanks khtan, I'll try using the Count keyword method instead|||

Provided you read the dataset first, you can also have output parameters on the stored procedure that gets your batch size. Have a look at http://www.4guysfromrolla.com/webtech/041206-1.shtml (Efficiently Paging Through Large Result Sets in SQL Server 2000)

|||

TATWORTH:

Provided you read the dataset first, you can also have output parameters on the stored procedure that gets your batch size. Have a look at http://www.4guysfromrolla.com/webtech/041206-1.shtml ( Efficiently Paging Through Large Result Sets in SQL Server 2000)

wow, thanks tatworth. I started on the tutorial link you supplied above and eventually found myself doing the tutorial (A more efficent method for paging through large result sets : link below)

http://www.4guysfromrolla.com/webtech/042606-1.shtml

But I've hit a problem. Like I say I've done the tut above and it works great...if you only want the results to be in order of id.

I'm trying to change the order by other fields (name, date of birth) but I'm not getting anywhere.

Any one any tips?

|||

Please always remember when using output parameters and a dataset, to read the dataset before the output parameters - there is a "bug"/"feature" that only populates the output parameters after the dataset has been read.

As to your sort problem, the solution pobably lies along using "date of birth and id" instead of just "date of birth" - please post an example of the revised TSQL together with a script to create the table. I will look at it and attempt a fix the problem; however it may not be until next weekend that I can look at it.

|||

TATWORTH:

Please always remember when using output parameters and a dataset, to read the dataset before the output parameters - there is a "bug"/"feature" that only populates the output parameters after the dataset has been read.

As to your sort problem, the solution pobably lies along using "date of birth and id" instead of just "date of birth" - please post an example of the revised TSQL together with a script to create the table. I will look at it and attempt a fix the problem; however it may not be until next weekend that I can look at it.

wow, thanks tatworth

This is the stored procedure I created from the tutorial above, like I say it works if the order by is id, anything else and it doesn't work

ALTER PROCEDURE uk_members
(
@.username VarChar(20),
@.startRowIndex int,
@.maximumRows int
)
AS
DECLARE @.first_id int

SET ROWCOUNT @.startRowIndex

SELECT @.first_id = id FROM uk_orders
WHERE (username=@.username) AND (itemstat <> 'DELETE') ORDER BY id

SET ROWCOUNT @.maximumRows

SELECT id, username, suppliername, product, quantity
FROM uk_orders WHERE
(id >= @.first_id) AND (username=@.username) AND (suppliername <> 'SMITHS') ORDER BY id

SET ROWCOUNT 0

I've been messing around with the code but not got anywhere.

I really appreciate your help and of course anyone else offering any help is also really appreciated.