Log shipping question.

Hi all,

I have a question about the scheduling of different backup types under log
shipping.  Let's say I have two servers, ServerA and ServerB, and there's
one database called DatabaseA that exists on both servers.  Imagine that I
have it setup so that a full backup is generated every day at midnight,
differential backups every hour, and a transaction log every 15 minutes.
These are copied over to ServerB, and restored into the database as they
come in.  Thus, my database on ServerB is (at most) about 15 minutes behind.

Question: is there any advantage, given this scenario, to doing the full
backup every night versus once a week?  For that matter, why even do the
differentials every hour?  What if I changed it to do the full backup once a
week, differentials once a day, and kept generating the transaction logs
every 15 minutes?

It seems to me that, so long as I keep the transaction logs in order and
restore them as they are deposited on ServerB, my data would be just as
up-to-date as if I did the full and differentials more often -- plus, I'd be
generating less network traffic, which is a bonus in my scenario.

I'd appreciate any thoughts or observations ...

Thanks for the help!

Wade

Wade  (wwegner23NOEMAILhot
mail.com)  writes:
> I have a question about the scheduling of different backup types under
> log shipping.  Let's say I have two servers, ServerA and ServerB, and
> there's one database called DatabaseA that exists on both servers.
> Imagine that I have it setup so that a full backup is generated every
> day at midnight, differential backups every hour, and a transaction log
> every 15 minutes. These are copied over to ServerB, and restored into
> the database as they come in.  Thus, my database on ServerB is (at most)
> about 15 minutes behind.
>
> Question: is there any advantage, given this scenario, to doing the full
> backup every night versus once a week?  For that matter, why even do the
> differentials every hour?  What if I changed it to do the full backup
> once a week, differentials once a day, and kept generating the
> transaction logs every 15 minutes?

Others have more experience of setting backup schedules than I have, but I
would keep the nightly full backup and the log backups, but skip the
differential backups.

One thing of interest here is how far ServerA and ServerB are from each
other. Are they on different sides of town, or in the same computer room?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Hi

You did not state your DB size, but running differentials every hours seems
a bit much to me.

Most people use the:
Full one per week
Differential once per day
Transaction log every 15 minutes.

For a restore, you need your last full backup, the most recent differential
and the transaction logs after the differential to the point in time.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Wade" < wwegner23NOEMAILhotm
ail.com> wrote in message
news:ON3vTRNrFHA.1128@TK2MSFTNGP11.phx.gbl...
> Hi all,
>
> I have a question about the scheduling of different backup types under log
> shipping.  Let's say I have two servers, ServerA and ServerB, and there's
> one database called DatabaseA that exists on both servers.  Imagine that I
> have it setup so that a full backup is generated every day at midnight,
> differential backups every hour, and a transaction log every 15 minutes.
> These are copied over to ServerB, and restored into the database as they
> come in.  Thus, my database on ServerB is (at most) about 15 minutes
> behind.
>
> Question: is there any advantage, given this scenario, to doing the full
> backup every night versus once a week?  For that matter, why even do the
> differentials every hour?  What if I changed it to do the full backup once
> a week, differentials once a day, and kept generating the transaction logs
> every 15 minutes?
>
> It seems to me that, so long as I keep the transaction logs in order and
> restore them as they are deposited on ServerB, my data would be just as
> up-to-date as if I did the full and differentials more often -- plus, I'd
> be generating less network traffic, which is a bonus in my scenario.
>
> I'd appreciate any thoughts or observations ...
>
> Thanks for the help!
>
> Wade
>

Thanks, Mike.

> You did not state your DB size, but running differentials every hours
> seems a bit much to me.

The sizes are all over the place -- 20 MBytes to 2.7 GBytes.  Obviously, if
there's not really a reason to do the fulls more often (which is what it
appears like ...), then the less frequenct the backups occur the better.

> Most people use the:
> Full one per week
> Differential once per day
> Transaction log every 15 minutes.

Yes, exactly what I stated.

> For a restore, you need your last full backup, the most recent
> differential and the transaction logs after the differential to the point
> in time.

... thanks.

Wade

> Others have more experience of setting backup schedules than I have, but I
> would keep the nightly full backup and the log backups, but skip the
> differential backups.

I've thought of that too ... but, it seems like having the diffs is a good
catch, because if something happens and the LSN #'s get out of sequence with
the transaction logs (which has happened to me in testing, although I think
I have that resolved) the differential backups can usually clean it up.

> One thing of interest here is how far ServerA and ServerB are from each
> other. Are they on different sides of town, or in the same computer room?

Denver, CO --> Reston, VA.  We have a 1 megabit pipe that we're replicating
data over.  Perhaps I should have added a bit more of the real world into
the question. :)

Thanks,

Wade

Wade  (wwegner23NOEMAILhot
mail.com)  writes: 
>
> Denver, CO --> Reston, VA.  We have a 1 megabit pipe that we're
> replicating data over.  Perhaps I should have added a bit more of the
> real world into the question. :)

That's good. It has to be pretty bug disaster that wipes out both.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

God forbid.

So ... kind sounds like there's no reason to backup anymore than the
following

Full: 1 / week
Diff: 1 / day
Tran: Every 15 minutes

Sound good?

Thanks!

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
 news:Xns96C2811C7115
3Yazorman@127.0.0.1...
> Wade  (wwegner23NOEMAILhot
mail.com) writes: 
>
> That's good. It has to be pretty bug disaster that wipes out both.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>