[SQL2005] Backup Schedule

23 Jan 2006 12:20 PM

PaulSand

I understand most things concerning making a backup, but I cannot seem to
figure out what a sensible schedule is for making a backup. I would love to
figure this out before we go live in a few months.

The database will have a reasonable mutation rate, i.e. around 500 data
changes per day and work-loss must be kept to a minimum. We expect the size
to be around 2GB. The schedule I have made now is as follows:

Full Backup on Sunday at 02:00
Differential Backups on other weekdays at 02:00
Transaction Log Backups every 4 hours every day
Clean History 4 weeks old files every week on Sunday at 04:00

I do not know if this makes sense. I do not know if the log backup proces
will hinder the users or not. I do know there are many many variables that
play a role in making a good backup procedure, but I would greatly appreciate
any insight concerning correct scheduling. TIA!

23 Jan 2006 1:37 PM

Tibor Karaszi

I would definitely change things a bit, considering "work-loss must be kept to a minimum".

How about database backup every day and log backup at least every hour. Or log backups every 10
minutes? The more frequent you do log backup, the less impact is will have each time it is executed
(even though the impact is low to begin with, especially if you have separated log/data/backup
disks). For a 2 GB database, I probably wouldn't do differential backups, unless you have compelling
advantages to do so.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quoteHide quote

"PaulSand" <PaulS***@discussions.microsoft.com> wrote in message
news:E2D4C305-A7A2-4651-A4DB-CE04E247F5FB@microsoft.com...
>I understand most things concerning making a backup, but I cannot seem to
> figure out what a sensible schedule is for making a backup. I would love to
> figure this out before we go live in a few months.
>
> The database will have a reasonable mutation rate, i.e. around 500 data
> changes per day and work-loss must be kept to a minimum. We expect the size
> to be around 2GB. The schedule I have made now is as follows:
>
> Full Backup on Sunday at 02:00
> Differential Backups on other weekdays at 02:00
> Transaction Log Backups every 4 hours every day
> Clean History 4 weeks old files every week on Sunday at 04:00
>
> I do not know if this makes sense. I do not know if the log backup proces
> will hinder the users or not. I do know there are many many variables that
> play a role in making a good backup procedure, but I would greatly appreciate
> any insight concerning correct scheduling. TIA!

23 Jan 2006 1:48 PM

Corey Bunch

I agree with Tibor. Although I'm sure useful in some scenarios, we
don't use differentials at all. Fulls once per day, and then logs
every hour to two hours, is what we do. It's easier to manage the
files, and we can keep track in our head which files exactly we need in
the case of restore. And we're in the same boat - "work-loss must be
kept to a minimum".

Tibor Karaszi wrote:
Show quoteHide quote

> I would definitely change things a bit, considering "work-loss must be kept to a minimum".
>
> How about database backup every day and log backup at least every hour. Or log backups every 10
> minutes? The more frequent you do log backup, the less impact is will have each time it is executed
> (even though the impact is low to begin with, especially if you have separated log/data/backup
> disks). For a 2 GB database, I probably wouldn't do differential backups, unless you have compelling
> advantages to do so.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
>
> "PaulSand" <PaulS***@discussions.microsoft.com> wrote in message
> news:E2D4C305-A7A2-4651-A4DB-CE04E247F5FB@microsoft.com...
> >I understand most things concerning making a backup, but I cannot seem to
> > figure out what a sensible schedule is for making a backup. I would love to
> > figure this out before we go live in a few months.
> >
> > The database will have a reasonable mutation rate, i.e. around 500 data
> > changes per day and work-loss must be kept to a minimum. We expect the size
> > to be around 2GB. The schedule I have made now is as follows:
> >
> > Full Backup on Sunday at 02:00
> > Differential Backups on other weekdays at 02:00
> > Transaction Log Backups every 4 hours every day
> > Clean History 4 weeks old files every week on Sunday at 04:00
> >
> > I do not know if this makes sense. I do not know if the log backup proces
> > will hinder the users or not. I do know there are many many variables that
> > play a role in making a good backup procedure, but I would greatly appreciate
> > any insight concerning correct scheduling. TIA!