Backup practices ...

Isn't it better to backup databases and transaction logs to a different
drive than the data files? Both for reliability (if the drive dies you're
hurting since the backup is there as well) and effeciency (less contention
on the data drive). If you're backing up everything to the same drive as the
data, can this cause processor spikes? We do backup to tape as well, but
this is not as fast to recover from when there are a zillion users.
As you probably guessed, some pre-existing maintence plans are set up this
way and I'm thinking I should push to get them changed. But this may require
additional hardware so somebody has to write a check. You know the drill.
Thanks,
Bob Castleman
DBA Poseur"Bob Castleman" <nomail@.here> wrote in message
news:e$madNqaFHA.3684@.TK2MSFTNGP12.phx.gbl...
> Isn't it better to backup databases and transaction logs to a different
> drive than the data files? Both for reliability (if the drive dies you're
> hurting since the backup is there as well) and effeciency (less contention
> on the data drive). If you're backing up everything to the same drive as
the
> data, can this cause processor spikes? We do backup to tape as well, but
> this is not as fast to recover from when there are a zillion users.
> As you probably guessed, some pre-existing maintence plans are set up this
> way and I'm thinking I should push to get them changed. But this may
require
> additional hardware so somebody has to write a check. You know the drill.
> Thanks,
> Bob Castleman
> DBA Poseur
>
Actually Bob, you should always do your backups to the same local drives as
the active databases are on. In fact, I'm not sure why you are even
bothering with doing backups at all... <wink>
You are correct, a good backup (and recovery) strategy should be
implemented. Backing up to the same hard disks is dependent on a lot of
different factors.
1. How much activity is going on during the backup process?
2. How big are the backups that we are talking about?
3. How much network traffic do you have already (if you wanted to push your
backups to a UNC name on a different computer).
That said, having your backups in contention with the active databases on
the same physical drives is almost never a good idea (for the reasons you
listed).
Rick Sawtell|||
> Actually Bob, you should always do your backups to the same local drives
> as
> the active databases are on. In fact, I'm not sure why you are even
> bothering with doing backups at all... <wink>
Facetiously sarcastic. Gotta luv it!

> 1. How much activity is going on during the backup process?
Log backup during the production day every four hours spike the processor
pretty hard. Full backups at night are not an issues, yet.

> 2. How big are the backups that we are talking about?
Several hundred databases. Transaction logs every four hours total about a
gig. Full nightly backups are about 100 gig.

> 3. How much network traffic do you have already (if you wanted to push
> your
> backups to a UNC name on a different computer).
>
None right now. Backups are over a fiber chanel to the RAID and so far
network traffic to the database servers is very low.|||"Bob Castleman" <nomail@.here> wrote in message
news:uKsEHrqaFHA.220@.TK2MSFTNGP10.phx.gbl...
>
> Log backup during the production day every four hours spike the processor
> pretty hard. Full backups at night are not an issues, yet.
>
Well, if you can't get the new hardware, how about doing TLog dumps more
often. You still have the pay the piper for the CPU time, but if they were
every hour instead of every 4 hours, your spikes should be shorter-lived.

> None right now. Backups are over a fiber chanel to the RAID and so far
> network traffic to the database servers is very low.
>
Very nice!!!
Rick Sawtell