Backup strategy

[Jay]

I've recently inherited a position where I am responsible for the well-being
of some DBs.
2 (much) more important than others.
The current recovery model, from what I can tell, is to do a full db/log
backup overnight.
This .bak file is then written to tape as well as saved on the disk for 2
days.

Both these dbs are used fairly extensively 8-5pm and losing data would not
be good.
The db sizes are approx 5gb and 3gb.
This doesn't seem like the ideal situation to me. Everything I read tells
me... full backup periodically, differential nightly and transaction hourly.
Agreed?
If so then I have 2 questions:
1. Is the best way to do this via a maintenance plan or by scripting and
scheduling?
2. What, if any, overhead can be expected with regular transaction backups
during work hours?

A bit of a pointer to #1 would be appreciated also.
Thanks.

[Simon Hayes]

Re: Backup strategy

Both the databases are quite small, so using full, differential and log
backups may be overkill - you might find that a full backup once or
twice a day with log backups every hour (or whatever) is fine. On the
other hand, if the databases are growing fast, then you may want to
allow for that from the beginning.

In my opinion, maintenance plans are useful for quickly getting backups
running in a new environment, but their functionality and flexibility
can be limited. So I would probably use them as a starting point while
I worked out what the longer term backup plan should be. For example, I
don't believe that maintenance plans support differential backups, so
if you need them then you would have to create your own jobs anyway.
See "Reducing Recovery Time" in Books Online for details of using
different backup types together.

The overhead of a transaction log backup is likely to be minimal, but
as always, it's best to test it yourself in your own environment. It
would depend on the number of transactions, the disk layout and the
destination for the backups etc. But even if there is some slight
impact, it's probably a small price to pay for having the backups
available in the case of an outage.

Simon

[Oscar Santiesteban Jr.]

Re: Backup strategy

Jay,

The only thing I use the maintenance jobs for are two (2) tasks.
1. checking the consistency of the database (usually at 11pm on Sat)
2. doing the reindex of the database (usally at 1 am on Fri)

The rest of the backups I script manually. I create a backup device for
each database.
Job #1 - Full Backup of the database (usually at 6pm each night)
Job #2 - Transaction log backup every hour from 7am to 5pm.
Job #3 - Full backup of the database to a separate device at 12 noon (not
all databases)

Oscar....

"Jay" <jazemail@gmail .com> wrote in message
news:Hqepe.5753 $U4.811596@news .xtra.co.nz...[color=blue]
> I've recently inherited a position where I am responsible for the[/color]
well-being[color=blue]
> of some DBs.
> 2 (much) more important than others.
> The current recovery model, from what I can tell, is to do a full db/log
> backup overnight.
> This .bak file is then written to tape as well as saved on the disk for 2
> days.
>
> Both these dbs are used fairly extensively 8-5pm and losing data would not
> be good.
> The db sizes are approx 5gb and 3gb.
> This doesn't seem like the ideal situation to me. Everything I read tells
> me... full backup periodically, differential nightly and transaction[/color]
hourly.[color=blue]
> Agreed?
> If so then I have 2 questions:
> 1. Is the best way to do this via a maintenance plan or by scripting and
> scheduling?
> 2. What, if any, overhead can be expected with regular transaction backups
> during work hours?
>
> A bit of a pointer to #1 would be appreciated also.
> Thanks.
>
>[/color]

[Jay]

Re: Backup strategy


"Oscar Santiesteban Jr." <oscarsantieste ban@worldnet.at t.net> wrote in
message news:Qgtpe.9126 01$w62.11314@bg tnsc05-news.ops.worldn et.att.net...[color=blue]
> Jay,
>
> The only thing I use the maintenance jobs for are two (2) tasks.
> 1. checking the consistency of the database (usually at 11pm on Sat)
> 2. doing the reindex of the database (usally at 1 am on Fri)[/color]

Forgive me if I don't follow your practise. Those hours are the only hours
I'm *not* thinking about DBs :)
I've learned that the transaction log is written to 2 HDD (raid) so it isn't
as bad as I thought.
As a precaution I now back up the transaction log to a USB drive each
lunchtime.

Cheers
Jay

[color=blue]
> The rest of the backups I script manually. I create a backup device for
> each database.
> Job #1 - Full Backup of the database (usually at 6pm each night)
> Job #2 - Transaction log backup every hour from 7am to 5pm.
> Job #3 - Full backup of the database to a separate device at 12 noon (not
> all databases)
>
> Oscar....
>
> "Jay" <jazemail@gmail .com> wrote in message
> news:Hqepe.5753 $U4.811596@news .xtra.co.nz...[color=green]
>> I've recently inherited a position where I am responsible for the[/color]
> well-being[color=green]
>> of some DBs.
>> 2 (much) more important than others.
>> The current recovery model, from what I can tell, is to do a full db/log
>> backup overnight.
>> This .bak file is then written to tape as well as saved on the disk for 2
>> days.
>>
>> Both these dbs are used fairly extensively 8-5pm and losing data would
>> not
>> be good.
>> The db sizes are approx 5gb and 3gb.
>> This doesn't seem like the ideal situation to me. Everything I read tells
>> me... full backup periodically, differential nightly and transaction[/color]
> hourly.[color=green]
>> Agreed?
>> If so then I have 2 questions:
>> 1. Is the best way to do this via a maintenance plan or by scripting and
>> scheduling?
>> 2. What, if any, overhead can be expected with regular transaction
>> backups
>> during work hours?
>>
>> A bit of a pointer to #1 would be appreciated also.
>> Thanks.
>>
>>[/color]
>
>[/color]

[Greg D. Moore \(Strider\)]

Re: Backup strategy


"Jay" <jazemail@gmail .com> wrote in message
news:Hqepe.5753 $U4.811596@news .xtra.co.nz...[color=blue]
> I've recently inherited a position where I am responsible for the[/color]
well-being[color=blue]
> of some DBs.
> 2 (much) more important than others.
> The current recovery model, from what I can tell, is to do a full db/log
> backup overnight.
> This .bak file is then written to tape as well as saved on the disk for 2
> days.
>
> Both these dbs are used fairly extensively 8-5pm and losing data would not
> be good.
> The db sizes are approx 5gb and 3gb.
> This doesn't seem like the ideal situation to me. Everything I read tells
> me... full backup periodically, differential nightly and transaction[/color]
hourly.[color=blue]
> Agreed?
> If so then I have 2 questions:
> 1. Is the best way to do this via a maintenance plan or by scripting and
> scheduling?[/color]

For this I'd use a simple maintenance plan to back up your logs every X
minutes.

[color=blue]
> 2. What, if any, overhead can be expected with regular transaction backups
> during work hours?
>[/color]

VERY little.

We run our transaction log backups every 20 minutes and notice no impact.

(in fact, ironically in some cases, running them MORE often can be better
since they'll be smaller when you do run them.)

[color=blue]
> A bit of a pointer to #1 would be appreciated also.
> Thanks.
>
>[/color]