Very Computer

Hi,

I'm trying to learn SQL server backup, and now understand the difference
between "Simple" and "Full" recovery modes.

I've created two maintenance plans; one for "system" DBs, and one for
selected "user" DBs.

USER DBs:
All DBs set to "Full" recovery. Full database backup every Friday at
6pm. Transaction log backups every night at 6:15pm _including_ Friday.
8pm every night the whole \backup folder goes to DLT. In other words
it's one full backup per week with a daily transaction log.

SYSTEM DBs:
Full database backup of master, model and msdb to default backup folder
6:30pm Friday night.

I note "model" is set to Full recovery, but I'm not backing up it's
transaction log.

My concern is that a full backup once per week may be cutting it a bit
fine. I mean if my transaction log on Monday's tape can't be restored
for some reason I could lose a weeks work!

--
Gerry Hickman (London UK)

We backup all databases nightly.
For our user databases we do transaction log backups throughout the day at
an interval that we feel comfortable with -- also at an interval that allows
frequent restores to our reporting database server (warm standby server).

I feel that you need to increase your transaction log backups (at a
minimum)!

--
Keith, SQL Server MVP

Quote:> Hi,

> I'm trying to learn SQL server backup, and now understand the difference
> between "Simple" and "Full" recovery modes.

> I've created two maintenance plans; one for "system" DBs, and one for
> selected "user" DBs.

> USER DBs:
> All DBs set to "Full" recovery. Full database backup every Friday at
> 6pm. Transaction log backups every night at 6:15pm _including_ Friday.
> 8pm every night the whole \backup folder goes to DLT. In other words
> it's one full backup per week with a daily transaction log.

> SYSTEM DBs:
> Full database backup of master, model and msdb to default backup folder
> 6:30pm Friday night.

> I note "model" is set to Full recovery, but I'm not backing up it's
> transaction log.

> My concern is that a full backup once per week may be cutting it a bit
> fine. I mean if my transaction log on Monday's tape can't be restored
> for some reason I could lose a weeks work!

> --
> Gerry Hickman (London UK)

I wouldn't be happy with anything less than daily full backups
and hourly transaction log backups. On  our OLTP
databases we do tran log backups every 20 minutes which
we log ship to our Standby Server. It depends on the
transaction rate of your databases - some can get away with
just full daily backups whilst others need frequent log backups
Also try and regularly test your backups by restoring them
Nothing worse than a tape full of useless backups :-)

HTH
Jasper Smith

We backup all databases nightly.
For our user databases we do transaction log backups throughout the day at
an interval that we feel comfortable with -- also at an interval that allows
frequent restores to our reporting database server (warm standby server).

I feel that you need to increase your transaction log backups (at a
minimum)!

--
Keith, SQL Server MVP

Quote:> Hi,

> I'm trying to learn SQL server backup, and now understand the difference
> between "Simple" and "Full" recovery modes.

> I've created two maintenance plans; one for "system" DBs, and one for
> selected "user" DBs.

> USER DBs:
> All DBs set to "Full" recovery. Full database backup every Friday at
> 6pm. Transaction log backups every night at 6:15pm _including_ Friday.
> 8pm every night the whole \backup folder goes to DLT. In other words
> it's one full backup per week with a daily transaction log.

> SYSTEM DBs:
> Full database backup of master, model and msdb to default backup folder
> 6:30pm Friday night.

> I note "model" is set to Full recovery, but I'm not backing up it's
> transaction log.

> My concern is that a full backup once per week may be cutting it a bit
> fine. I mean if my transaction log on Monday's tape can't be restored
> for some reason I could lose a weeks work!

> --
> Gerry Hickman (London UK)

Thanks Keith / Jasper,

The thing about 10 minute transaction logs: we can only stream to tape
once per day, so would we actually gain anything from this?

With the above in mind, would I be better off just doing "Simple"
backups once per day of the whole databases instead of messing around
with transaction logs?

Losing a day's work would be a pain, but none of our other data is safe
between daily backups anyway. We're not an e-commerce site, but we do
have constant user connections throughout the day, albeit with a small
number of users (about 500 per day read-only and about 10 per day
actually updating).

--
Gerry Hickman (London UK)

But if you have a couple of disks fail and assuming
you are not backing up to the same set of disks with
your backups then you'd still have them to hand. We
actually backup to disk, log ship to standby and zip and
copy the backups to a separate physical file server
(paranoia :-) and it only gets onto tape at night. The most
likely failure on a server is probably a single disk failure
(although even those are rare) and so transaction log backups
give you protection (as well as hardware RAID).
I'd suggest full nightly backup and tlogs every hour.
With the maintenance plan wizard this is relatively simple to
impliment and fairly automatic. Then you just dump your
backup folder to tape at night

HTH
Jasper Smith

Thanks Keith / Jasper,

The thing about 10 minute transaction logs: we can only stream to tape
once per day, so would we actually gain anything from this?

With the above in mind, would I be better off just doing "Simple"
backups once per day of the whole databases instead of messing around
with transaction logs?

Losing a day's work would be a pain, but none of our other data is safe
between daily backups anyway. We're not an e-commerce site, but we do
have constant user connections throughout the day, albeit with a small
number of users (about 500 per day read-only and about 10 per day
actually updating).

--
Gerry Hickman (London UK)

Hi Jasper,

Quote:> But if you have a couple of disks fail and assuming
> you are not backing up to the same set of disks with
> your backups then you'd still have them to hand.

Erm? I've read that 3 times, am I'm still not sure I understand!

Quote:> We
> actually backup to disk, log ship to standby and zip and
> copy the backups to a separate physical file server
> (paranoia :-) and it only gets onto tape at night.

Cool.

Quote:> The most
> likely failure on a server is probably a single disk failure

Right.

Quote:> (although even those are rare) and so transaction log backups
> give you protection (as well as hardware RAID).

I've got hardware RAID.

Quote:> I'd suggest full nightly backup and tlogs every hour.

This is where I get a bit lost. I'm trying to think how these logs will
help if I'm only backing up to tape once per day? I guess you're saying
that I should have more than just tape backup to rely on? e.g. I should
actually stream the logs to an other server once per hour? If that's the
case, that's interesting as that was along the lines of my original plan
but I ran into trouble trying to use UNC paths as I currently have the
services accounts set to SYSTEM; I'll change them to domain accounts if
I have to.

Thing is, let's say I'm only using tape for a minute; surely it makes no
difference (in that context) whether I have 24 tLogs or just 1 or 2?
Surely it's quicker to do a restore with just one or two logs as opposed
to 24!

--
Gerry Hickman (London UK)

I'm basically saying do your backups to a separate set of disks i.e.
ones that aren't used for dat/log/sql/windows
This way a failure that wipes your log array or your data array will
hopefully not wipeout your backups.

In the grand scheme of things the most likely DR situation is a single
part failure of a server - not the whole server getting wiped out. In
this case, whether the backups are on tape or not is irrelavent as they
are still on disk and can be recovered. If you want to be safer than
this - spread them out onto 1 or more separate physical servers (as you
rightly state this requires SQL Services to run under a vaild domain
user account in order to access shares if you want to control the copy
from within a sql job)

If you are only backing up to tape and never to disk and you can only
backup once a day then full daily backups would be your only option

Cheers
Jasper Smith

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks Jasper,

Well at least I understand the issues now, and will have to decide what
to do. Just one small question, when you say "if you're only backing up
to tape once a day, you should do full daily backups", does this mean I
should switch to "Simple" recovery mode for user DBs? Will this restart
the tLog and keep the full backups "clean"?

--
Gerry Hickman (London UK)