advice on creating a backup plan

Could someone give me some feedback on the backup plan that I have created
for my companies SQL Server. First a little background.
We have 1 win2k advance server running both IIS and our SQL Server. The
database held on the server is the backend to our e-commerce site. The db
needs to be up 24-7 and typically sees the most usage from 9am - 11pm EST.
Atleast half of the tables in the db are modified through the day. There are
no large bulk insertions or deletions done through the day. The MDF and LDF
files are on different hd's.
I would like to create a plan so that in case of massive hardware failur we
could have a complete up-to-date copy of the db back up in less than 4
hours. I would like to backup up my sql databases and the Win2k system
state. The database recovery method is set to FULL.
The backup cycle starts at 6:00 am EST with a complete backup of the
database. Every 2 hours after the complete backup the transaction log is
backed up. Every 6 hours after the complete backup a Differential DB backup
is made.
At 5:00 am the next day those files are copied to another drive and the
cycle starts again with the complete db backup file overwriting itself.
This way I will always have a complete backup of the following day. I also
copy the backed up backups offline to a different server for archiving.
The system db's are backedup every week. Integrity checks are done every
week as well.
Is there anything else I should do like re create indexes as part of my
plan? How often should I back up the system state? Rigth now I back it up
once a week.
Any comments would be appreciated as preventing the loss of data is of the
utmost importance. Thanks in advance.The big thing I see regards WHERE the backups are kept...If you are leaving
them on the SQL box all day, change the plan... backup things to a different
box, and get the backups off of the SQL Server...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"news.microsoft.com" <cramsundar@.mail.com> wrote in message
news:OBt4tPpjDHA.2244@.TK2MSFTNGP12.phx.gbl...
> Could someone give me some feedback on the backup plan that I have created
> for my companies SQL Server. First a little background.
> We have 1 win2k advance server running both IIS and our SQL Server. The
> database held on the server is the backend to our e-commerce site. The db
> needs to be up 24-7 and typically sees the most usage from 9am - 11pm EST.
> Atleast half of the tables in the db are modified through the day. There
are
> no large bulk insertions or deletions done through the day. The MDF and
LDF
> files are on different hd's.
> I would like to create a plan so that in case of massive hardware failur
we
> could have a complete up-to-date copy of the db back up in less than 4
> hours. I would like to backup up my sql databases and the Win2k system
> state. The database recovery method is set to FULL.
> The backup cycle starts at 6:00 am EST with a complete backup of the
> database. Every 2 hours after the complete backup the transaction log is
> backed up. Every 6 hours after the complete backup a Differential DB
backup
> is made.
> At 5:00 am the next day those files are copied to another drive and the
> cycle starts again with the complete db backup file overwriting itself.
> This way I will always have a complete backup of the following day. I
also
> copy the backed up backups offline to a different server for archiving.
> The system db's are backedup every week. Integrity checks are done every
> week as well.
> Is there anything else I should do like re create indexes as part of my
> plan? How often should I back up the system state? Rigth now I back it
up
> once a week.
> Any comments would be appreciated as preventing the loss of data is of the
> utmost importance. Thanks in advance.
>|||We do not have another box to back up the data too. We have a very small
operation. The backups reside on two different hds and will be copied off
the server.
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:Oh4WL2pjDHA.1284@.TK2MSFTNGP09.phx.gbl...
> The big thing I see regards WHERE the backups are kept...If you are
leaving
> them on the SQL box all day, change the plan... backup things to a
different
> box, and get the backups off of the SQL Server...
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "news.microsoft.com" <cramsundar@.mail.com> wrote in message
> news:OBt4tPpjDHA.2244@.TK2MSFTNGP12.phx.gbl...
> > Could someone give me some feedback on the backup plan that I have
created
> > for my companies SQL Server. First a little background.
> >
> > We have 1 win2k advance server running both IIS and our SQL Server. The
> > database held on the server is the backend to our e-commerce site. The
db
> > needs to be up 24-7 and typically sees the most usage from 9am - 11pm
EST.
> > Atleast half of the tables in the db are modified through the day. There
> are
> > no large bulk insertions or deletions done through the day. The MDF and
> LDF
> > files are on different hd's.
> >
> > I would like to create a plan so that in case of massive hardware failur
> we
> > could have a complete up-to-date copy of the db back up in less than 4
> > hours. I would like to backup up my sql databases and the Win2k system
> > state. The database recovery method is set to FULL.
> >
> > The backup cycle starts at 6:00 am EST with a complete backup of the
> > database. Every 2 hours after the complete backup the transaction log is
> > backed up. Every 6 hours after the complete backup a Differential DB
> backup
> > is made.
> >
> > At 5:00 am the next day those files are copied to another drive and the
> > cycle starts again with the complete db backup file overwriting itself.
> > This way I will always have a complete backup of the following day. I
> also
> > copy the backed up backups offline to a different server for archiving.
> >
> > The system db's are backedup every week. Integrity checks are done
every
> > week as well.
> >
> > Is there anything else I should do like re create indexes as part of my
> > plan? How often should I back up the system state? Rigth now I back it
> up
> > once a week.
> >
> > Any comments would be appreciated as preventing the loss of data is of
the
> > utmost importance. Thanks in advance.
> >
> >
>sql