Backup plan reccomendations for user with ZERO experience.

[scott s]

Hello all, I was just awarded the job of maintaing the database server
for our company. I have basically ZERO experience using MS SQL Server
2000. Can anyone point me in the direction of a good resource for
creating backups of our database? I would love something that comes
with a gui that really simplifies the process; seeing as how i have
never even opened the MS SQL program.

Our database is fairly small we have 7 users with access to the
database. That is it.

any advice or good resources would be greatly appreciated.

[rcamarda]

Look into Enterprise Manager, Maintenance Plan Wizard. It will help
you with various tasks including backups.
In my backup jobs, I first perform a log backup then a full backup.
This will keep the size of the transaction log in check as it gets
truncated during this process. (Assuming the database is in FULL and
not SIMPLE. Check the properties of the database via EM).

I found that when I only did a full backup, my transaction log kept
growing.
MC2
Rob

On Aug 14, 10:49 pm, scott s <[e-mail]:

Hello all, I was just awarded the job of maintaing the database server
for our company. I have basically ZERO experience using MS SQL Server
2000. Can anyone point me in the direction of a good resource for
creating backups of our database? I would love something that comes
with a gui that really simplifies the process; seeing as how i have
never even opened the MS SQL program.

Our database is fairly small we have 7 users with access to the
database. That is it.

any advice or good resources would be greatly appreciated.

[Erland Sommarskog]

scott s (sc**********@gmail.com) writes:

Hello all, I was just awarded the job of maintaing the database server
for our company. I have basically ZERO experience using MS SQL Server
2000. Can anyone point me in the direction of a good resource for
creating backups of our database? I would love something that comes
with a gui that really simplifies the process; seeing as how i have
never even opened the MS SQL program.

Our database is fairly small we have 7 users with access to the
database. That is it.

any advice or good resources would be greatly appreciated.

First you need to determine which recovery model you want to use. There
are three to choose from FULL, BULK_LOGGED and SIMPLE. BULK_LOGGED is really
a variation of FULL, and we can leave it out for now.

In FULL recovery mode you are able to recover from a failure to any point
in time. In SIMPLE recovery, you can only recover by restoring the last
backup. So the question you need to answer: if you take a full backup
at midight, and at 15:00 something bad happens (disk crash, an UPDATE
without a WHERE clause or similar), what is your business requirement:
being able to recover the state at 14:59? Or are you fully content with
restoring the backup from midnight?

If you are content with restoring from the most recent backup, make
sure the database is simple recovery:

ALTER DATABASE db SET RECOVERY SIMPLE

now all you have to bother about is setting up full backup that is
scheduled each night, and that the backup file is copied somewhere. You
should make sure that you have backups a couple of days back in time
available, in case some accident is not discovered in time, or if a
backup is unreadable for some reason.

If you need up-to-the-point recovery, you also need to take log backups,
and you need full recovery. It's important to understand that taking log
backups is independet from database backups. If you never back up the log,
it will grow and grow. Since you are unexperienced, the best may be to
set up database backups through a maintenance plan in Enterprise
Manager.

Once you have your backups, you should also test restoring them, so
that you know the procedure if disaster strikes.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx