New backup plan - comments welcome!

New backup plan - comments welcome!

Post by Gerry Hickma » Thu, 23 May 2002 04:01:32



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)

 
 
 

New backup plan - comments welcome!

Post by Keith Kratochvi » Thu, 23 May 2002 04:06:35


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)


 
 
 

New backup plan - comments welcome!

Post by Jasper Smit » Thu, 23 May 2002 04:58:08


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)

 
 
 

New backup plan - comments welcome!

Post by Gerry Hickma » Thu, 23 May 2002 05:36:57


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).


> 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



> > 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)
 
 
 

New backup plan - comments welcome!

Post by Jasper Smit » Thu, 23 May 2002 06:45:46


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).


> 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



> > 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)
 
 
 

New backup plan - comments welcome!

Post by Gerry Hickma » Thu, 23 May 2002 08:03:48


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)

 
 
 

New backup plan - comments welcome!

Post by Jasper Smit » Thu, 23 May 2002 20:42:52


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!

 
 
 

New backup plan - comments welcome!

Post by Gerry Hickma » Fri, 24 May 2002 05:08:51


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"?


> 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!

--
Gerry Hickman (London UK)
 
 
 

1. Advice Please (all comments welcome)

I am a bit of a new comer to sql server and would like a little bit of
feedback from any of you developers out there.

I have been asked to develop an application using SQL Server 2000 as the
back end and VB.Net for the front end. The software will keep track of
documents and files.  When a new piece of documentation is produced on
behalf of a client it's physical location is added to the database and a
scanned image is somehow stored on the system.

The problem I will have is that some of the data needs to be accessed
eventually by clients on the web.  They will log on and get a chance to view
their documents.

I could of course create a web based application but this would be a bit
slow.

Has anyone out there had experience of developing this type of application?

If so can anyone give me a bit of advice?  I wont be starting this for some
time yet but I would like to do a bit of research first.

Thanks in Advance

2. bound combo box

3. A few comments which I would welcome discussion on.(please)

4. Speeding up smart objects

5. source codes for unlimited levels of grouping, sorting and filtering -- comments VERY welcome

6. IF UPDATE(columnname) updates for any old column!

7. All comments welcome (best way to use a proyect in vpn)

8. Where can i get SQL Server 6.5

9. Business Objects OLAP Essbase Access - Comments Welcome

10. 2 hairy problems - comments REALLY welcome

11. Question Regarding SQL Server 7.0 with XML Preview (For Frank Mantek, if possible, other comments welcome)

12. Comments Welcome

13. Backup Device vs Backup in Maint Plan