Log Backups Vs Differentials

I'm making some changes and I want to be sure there is nothing that I'm
over looking. I have tested it, but I rarely see people mention that
they've done it this way....
In order to try to keep my databases in simple recovery mode ( so that
I don't have to worry AS much about space when re-indexing -- less
logging ). I'm changing some plans to utilize differentials as opposed
to log backups. In our situation, they are at most every two hours
apart, and the backups are not stored locally, so space is not an
issue.
I really don't see anything wrong with this, and I have tested it ( and
generally the diffs will restore quicker than the logs ) so I'm curious
if there is anything I'm missing as to why people don't do this more
often and then allow for less log expansion by leaving it in simple
mode.
Thanks,Rachel wrote:
> I'm making some changes and I want to be sure there is nothing that I'm
> over looking. I have tested it, but I rarely see people mention that
> they've done it this way....
> In order to try to keep my databases in simple recovery mode ( so that
> I don't have to worry AS much about space when re-indexing -- less
> logging ). I'm changing some plans to utilize differentials as opposed
> to log backups. In our situation, they are at most every two hours
> apart, and the backups are not stored locally, so space is not an
> issue.
> I really don't see anything wrong with this, and I have tested it ( and
> generally the diffs will restore quicker than the logs ) so I'm curious
> if there is anything I'm missing as to why people don't do this more
> often and then allow for less log expansion by leaving it in simple
> mode.
> Thanks,
>
Offhand, you can't do point-in-time restores without log backups, you
can't do log shipping without log backups. Also, just because you're
running in Simple mode does NOT mean your log files won't grow.
http://realsqlguy.com/serendipity/archives/14-When-Is-A-Transaction-Log-Not-A-Transaction-Log.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Disadvantages of not doing log backups include:
You cannot restore to point in time. This can only be done for log backups.
You cannot backup the log if one of the database files goes bad.
If you get a corrupt page (etc), you will not be able to get 0 data loss.
In general, if you can accept 2 hours worth of data loss and if you catch corruption problems
immediately, and act upon, it can be OK. Check these things against your SLA.
Another risk is:
1 Db backup
2 Diff backup
3 Corrupt page(s)
4 Diff backup
5 Diff backup
6 Diff backup
7 You now notice the bad page
You will only get a clean database by restoring up to point 2. If you had log backups, you could get
0 data loss.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rachel" <rachel.kinder@.fbfs.com> wrote in message
news:1158670910.520654.157590@.i42g2000cwa.googlegroups.com...
> I'm making some changes and I want to be sure there is nothing that I'm
> over looking. I have tested it, but I rarely see people mention that
> they've done it this way....
> In order to try to keep my databases in simple recovery mode ( so that
> I don't have to worry AS much about space when re-indexing -- less
> logging ). I'm changing some plans to utilize differentials as opposed
> to log backups. In our situation, they are at most every two hours
> apart, and the backups are not stored locally, so space is not an
> issue.
> I really don't see anything wrong with this, and I have tested it ( and
> generally the diffs will restore quicker than the logs ) so I'm curious
> if there is anything I'm missing as to why people don't do this more
> often and then allow for less log expansion by leaving it in simple
> mode.
> Thanks,
>|||Wow. Actually, you can restore to whatever your last differential
backup was, which is the same as whatever your last log backup was ( so
in either case if it were a log every two hours, that's your point in
time, and the same holds true for differentials. I've done it ).
I know it won't guarantee the log files will not grow at all, but it
will keep them within reason when doing things ( such as was noted ) as
re-indexing.
I have also tested the huge difference in this.
Thanks for the comments though.
Tracy McKibben wrote:
> Rachel wrote:
> > I'm making some changes and I want to be sure there is nothing that I'm
> > over looking. I have tested it, but I rarely see people mention that
> > they've done it this way....
> >
> > In order to try to keep my databases in simple recovery mode ( so that
> > I don't have to worry AS much about space when re-indexing -- less
> > logging ). I'm changing some plans to utilize differentials as opposed
> > to log backups. In our situation, they are at most every two hours
> > apart, and the backups are not stored locally, so space is not an
> > issue.
> >
> > I really don't see anything wrong with this, and I have tested it ( and
> > generally the diffs will restore quicker than the logs ) so I'm curious
> > if there is anything I'm missing as to why people don't do this more
> > often and then allow for less log expansion by leaving it in simple
> > mode.
> >
> > Thanks,
> >
> Offhand, you can't do point-in-time restores without log backups, you
> can't do log shipping without log backups. Also, just because you're
> running in Simple mode does NOT mean your log files won't grow.
> http://realsqlguy.com/serendipity/archives/14-When-Is-A-Transaction-Log-Not-A-Transaction-Log.html
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I see what you are saying, but we can ( obviously or we'd be doing more
frequent log backups ) accept two hours of data loss.
Also, I've not experience when a page gets corrupt( suspect database )
that it can be recovered to anything other than prior to that
corruption.
So I'd argue that your last scenario is also true with log backups.
But that is, admittedly something I've not had a lot of experience
with. I'd have to actually test that to see it with my own eyes,
unless you have a detailed example. ( not necessarily doubting you,
but I try to test everything so I don't have any surprises. I'm not
sure how to force a corrupt data page though ).
Suggestions?
Some of these databases had only one full and one log per day prior to
this. So all in all this is actually adding several points in time of
recovery.
Thanks.
Tibor Karaszi wrote:
> Disadvantages of not doing log backups include:
> You cannot restore to point in time. This can only be done for log backups.
> You cannot backup the log if one of the database files goes bad.
> If you get a corrupt page (etc), you will not be able to get 0 data loss.
> In general, if you can accept 2 hours worth of data loss and if you catch corruption problems
> immediately, and act upon, it can be OK. Check these things against your SLA.
>
> Another risk is:
> 1 Db backup
> 2 Diff backup
> 3 Corrupt page(s)
> 4 Diff backup
> 5 Diff backup
> 6 Diff backup
> 7 You now notice the bad page
> You will only get a clean database by restoring up to point 2. If you had log backups, you could get
> 0 data loss.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rachel" <rachel.kinder@.fbfs.com> wrote in message
> news:1158670910.520654.157590@.i42g2000cwa.googlegroups.com...
> > I'm making some changes and I want to be sure there is nothing that I'm
> > over looking. I have tested it, but I rarely see people mention that
> > they've done it this way....
> >
> > In order to try to keep my databases in simple recovery mode ( so that
> > I don't have to worry AS much about space when re-indexing -- less
> > logging ). I'm changing some plans to utilize differentials as opposed
> > to log backups. In our situation, they are at most every two hours
> > apart, and the backups are not stored locally, so space is not an
> > issue.
> >
> > I really don't see anything wrong with this, and I have tested it ( and
> > generally the diffs will restore quicker than the logs ) so I'm curious
> > if there is anything I'm missing as to why people don't do this more
> > often and then allow for less log expansion by leaving it in simple
> > mode.
> >
> > Thanks,
> >|||Rachel wrote:
> Wow. Actually, you can restore to whatever your last differential
> backup was, which is the same as whatever your last log backup was ( so
> in either case if it were a log every two hours, that's your point in
> time, and the same holds true for differentials. I've done it ).
Point-in-time with a log file works a little differently. Look up the
STOPAT clause of the RESTORE command. Your log backup may cover a 2
hour window, but you can have the restore stop at any point within that
2 hour window. Can't do that with a diff.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Actually, given our enterprise backup solution, I can't do that wiht a
log either.
I guess I've left that out. We have a third party enterprise wide
backup solution that I must utilize for these and I cannot specify the
exact restore command for that. I'm going back to the last backup,
period.
I guess that I'm very lucky because I've been doing this for almost 10
years ( back to sql 6.5 ) and the majority of the times I've had to
restore was due to problems that the users caused, not actual
corruption. Can you speak for some actual experiences of this?
Tracy McKibben wrote:
> Rachel wrote:
> > Wow. Actually, you can restore to whatever your last differential
> > backup was, which is the same as whatever your last log backup was ( so
> > in either case if it were a log every two hours, that's your point in
> > time, and the same holds true for differentials. I've done it ).
> Point-in-time with a log file works a little differently. Look up the
> STOPAT clause of the RESTORE command. Your log backup may cover a 2
> hour window, but you can have the restore stop at any point within that
> 2 hour window. Can't do that with a diff.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Actually, given our enterprise backup solution, I can't do that wiht a
log either.
I guess I've left that out. We have a third party enterprise wide
backup solution that I must utilize for these and I cannot specify the
exact restore command for that. I'm going back to the last backup,
period.
I guess that I'm very lucky because I've been doing this for almost 10
years ( back to sql 6.5 ) and the majority of the times I've had to
restore was due to problems that the users caused, not actual
corruption. Can you speak for some actual experiences of this?
Tracy McKibben wrote:
> Rachel wrote:
> > Wow. Actually, you can restore to whatever your last differential
> > backup was, which is the same as whatever your last log backup was ( so
> > in either case if it were a log every two hours, that's your point in
> > time, and the same holds true for differentials. I've done it ).
> Point-in-time with a log file works a little differently. Look up the
> STOPAT clause of the RESTORE command. Your log backup may cover a 2
> hour window, but you can have the restore stop at any point within that
> 2 hour window. Can't do that with a diff.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||> Also, I've not experience when a page gets corrupt( suspect database )
> that it can be recovered to anything other than prior to that
> corruption.
> So I'd argue that your last scenario is also true with log backups.
The usual response: "It depends". Say you have below scenario:
1: Db backup
2: Log backup
3: Page corruption occurs
4: Log backup
5: Log backup
You now discover the corruption. So you do:
6: Log backup, with NORECOVERY
You now restore 1, 2, 4, 5, 6. You probably argue that the corruption will be re-introduced by
restoring 4. This is not necessarily the case. The log contains logical information about the
modifications made in the database, not the physical pages. Sure, you can have a corruption "creep"
into the log, by several modifications of data which happened to be corrupted. I've ran this
discussion with some of the MS engineers, and got some varying responses (real-life scenarios). My
interpretation from those discussions is "Give it a try, it is likely that the corruption will not
be re-introduced, but check after the restores...".
The MS course 2790 has a lab where an exe file corrupts a page, do a log backup and then do a single
page restore (!!!, new 2005 feature) and then restore that log backup. Btw, the database is online
for the non-affected data during the restore process. The corrupter.exe used in this lab is the only
corrupter I know of.
Did I mention to read Paul Randal's blog? Google and you will find it. He is probably the main
authority on the subject, lots of good thoughs.
Btw, I'm not arguing that your strategy might be the best for you. You know your environment, the
SLA etc. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rachel" <rachel.kinder@.fbfs.com> wrote in message
news:1158672304.556782.137430@.d34g2000cwd.googlegroups.com...
>I see what you are saying, but we can ( obviously or we'd be doing more
> frequent log backups ) accept two hours of data loss.
> Also, I've not experience when a page gets corrupt( suspect database )
> that it can be recovered to anything other than prior to that
> corruption.
> So I'd argue that your last scenario is also true with log backups.
> But that is, admittedly something I've not had a lot of experience
> with. I'd have to actually test that to see it with my own eyes,
> unless you have a detailed example. ( not necessarily doubting you,
> but I try to test everything so I don't have any surprises. I'm not
> sure how to force a corrupt data page though ).
> Suggestions?
> Some of these databases had only one full and one log per day prior to
> this. So all in all this is actually adding several points in time of
> recovery.
> Thanks.
>
> Tibor Karaszi wrote:
>> Disadvantages of not doing log backups include:
>> You cannot restore to point in time. This can only be done for log backups.
>> You cannot backup the log if one of the database files goes bad.
>> If you get a corrupt page (etc), you will not be able to get 0 data loss.
>> In general, if you can accept 2 hours worth of data loss and if you catch corruption problems
>> immediately, and act upon, it can be OK. Check these things against your SLA.
>>
>> Another risk is:
>> 1 Db backup
>> 2 Diff backup
>> 3 Corrupt page(s)
>> 4 Diff backup
>> 5 Diff backup
>> 6 Diff backup
>> 7 You now notice the bad page
>> You will only get a clean database by restoring up to point 2. If you had log backups, you could
>> get
>> 0 data loss.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Rachel" <rachel.kinder@.fbfs.com> wrote in message
>> news:1158670910.520654.157590@.i42g2000cwa.googlegroups.com...
>> > I'm making some changes and I want to be sure there is nothing that I'm
>> > over looking. I have tested it, but I rarely see people mention that
>> > they've done it this way....
>> >
>> > In order to try to keep my databases in simple recovery mode ( so that
>> > I don't have to worry AS much about space when re-indexing -- less
>> > logging ). I'm changing some plans to utilize differentials as opposed
>> > to log backups. In our situation, they are at most every two hours
>> > apart, and the backups are not stored locally, so space is not an
>> > issue.
>> >
>> > I really don't see anything wrong with this, and I have tested it ( and
>> > generally the diffs will restore quicker than the logs ) so I'm curious
>> > if there is anything I'm missing as to why people don't do this more
>> > often and then allow for less log expansion by leaving it in simple
>> > mode.
>> >
>> > Thanks,
>> >
>|||Thanks, I realize you're not saying that in my scenario, but you have
me very curious...
Plus, just because I don't implement it doesn't mean I don't want/need
to understand it.
I will check out the blog and do some testing to see if I can recreate
the scenario ( or something similar ).
And search for real-world examples.
Thanks again ( oh, andwe're not in 2005 world yet here. )
Thanks,
Tibor Karaszi wrote:
> > Also, I've not experience when a page gets corrupt( suspect database )
> > that it can be recovered to anything other than prior to that
> > corruption.
> > So I'd argue that your last scenario is also true with log backups.
> The usual response: "It depends". Say you have below scenario:
> 1: Db backup
> 2: Log backup
> 3: Page corruption occurs
> 4: Log backup
> 5: Log backup
> You now discover the corruption. So you do:
> 6: Log backup, with NORECOVERY
> You now restore 1, 2, 4, 5, 6. You probably argue that the corruption will be re-introduced by
> restoring 4. This is not necessarily the case. The log contains logical information about the
> modifications made in the database, not the physical pages. Sure, you can have a corruption "creep"
> into the log, by several modifications of data which happened to be corrupted. I've ran this
> discussion with some of the MS engineers, and got some varying responses (real-life scenarios). My
> interpretation from those discussions is "Give it a try, it is likely that the corruption will not
> be re-introduced, but check after the restores...".
> The MS course 2790 has a lab where an exe file corrupts a page, do a log backup and then do a single
> page restore (!!!, new 2005 feature) and then restore that log backup. Btw, the database is online
> for the non-affected data during the restore process. The corrupter.exe used in this lab is the only
> corrupter I know of.
> Did I mention to read Paul Randal's blog? Google and you will find it. He is probably the main
> authority on the subject, lots of good thoughs.
> Btw, I'm not arguing that your strategy might be the best for you. You know your environment, the
> SLA etc. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rachel" <rachel.kinder@.fbfs.com> wrote in message
> news:1158672304.556782.137430@.d34g2000cwd.googlegroups.com...
> >I see what you are saying, but we can ( obviously or we'd be doing more
> > frequent log backups ) accept two hours of data loss.
> > Also, I've not experience when a page gets corrupt( suspect database )
> > that it can be recovered to anything other than prior to that
> > corruption.
> > So I'd argue that your last scenario is also true with log backups.
> > But that is, admittedly something I've not had a lot of experience
> > with. I'd have to actually test that to see it with my own eyes,
> > unless you have a detailed example. ( not necessarily doubting you,
> > but I try to test everything so I don't have any surprises. I'm not
> > sure how to force a corrupt data page though ).
> > Suggestions?
> >
> > Some of these databases had only one full and one log per day prior to
> > this. So all in all this is actually adding several points in time of
> > recovery.
> >
> > Thanks.
> >
> >
> > Tibor Karaszi wrote:
> >> Disadvantages of not doing log backups include:
> >>
> >> You cannot restore to point in time. This can only be done for log backups.
> >>
> >> You cannot backup the log if one of the database files goes bad.
> >>
> >> If you get a corrupt page (etc), you will not be able to get 0 data loss.
> >>
> >> In general, if you can accept 2 hours worth of data loss and if you catch corruption problems
> >> immediately, and act upon, it can be OK. Check these things against your SLA.
> >>
> >>
> >> Another risk is:
> >>
> >> 1 Db backup
> >> 2 Diff backup
> >> 3 Corrupt page(s)
> >> 4 Diff backup
> >> 5 Diff backup
> >> 6 Diff backup
> >> 7 You now notice the bad page
> >>
> >> You will only get a clean database by restoring up to point 2. If you had log backups, you could
> >> get
> >> 0 data loss.
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Rachel" <rachel.kinder@.fbfs.com> wrote in message
> >> news:1158670910.520654.157590@.i42g2000cwa.googlegroups.com...
> >> > I'm making some changes and I want to be sure there is nothing that I'm
> >> > over looking. I have tested it, but I rarely see people mention that
> >> > they've done it this way....
> >> >
> >> > In order to try to keep my databases in simple recovery mode ( so that
> >> > I don't have to worry AS much about space when re-indexing -- less
> >> > logging ). I'm changing some plans to utilize differentials as opposed
> >> > to log backups. In our situation, they are at most every two hours
> >> > apart, and the backups are not stored locally, so space is not an
> >> > issue.
> >> >
> >> > I really don't see anything wrong with this, and I have tested it ( and
> >> > generally the diffs will restore quicker than the logs ) so I'm curious
> >> > if there is anything I'm missing as to why people don't do this more
> >> > often and then allow for less log expansion by leaving it in simple
> >> > mode.
> >> >
> >> > Thanks,
> >> >
> >