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
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
> 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
> frequent restores to our reporting database server (warm standby server).
> I feel that you need to increase your transaction log backups (at a
> Keith, SQL Server MVP
> > 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)
Gerry Hickman (London UK)