|
|
Posted by Erland Sommarskog on 08/15/07 21:38
scott s (scott.streit@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|