On 28 Oct 2003 15:57:10 -0800,
ja**********@perisher.com.au (trotter)
wrote:
I want to know if there is a "best-practice" for setting up Database
Maintenance Plans in SQL Server 7 or 2000. To be more specific, I want
to know the order in which I complete the tasks. Do I complete
optimization first, then integrity checks, then translog backup, then
full backup??? OR is there a better order which should be used?
Well, optimization means "changing things", and when you change
things, disasters are possible. Full backups come before
optimizations. Integrity *checks*, that is, looking to see if
everything is okay, should probably come before backup (you don't want
to back up bad data).
Should I ALWAYS backup the transaction Log before I complete a full
database backup, and if so, why??
Here's the deal on that.
The transaction log is a listing of what has been done (or is being
done) to the database. Once you've done a full backup, the transaction
log is no longer necessary to restore your database to the state it
was in as of the completion of the backup. So, if you backup the
database at 3:00am, the transaction log will do you no good in
restoring the database to the point that it was at 3:00am.
Okay... so why backup the transaction log at all?
Well, first, it backs up a lot faster than the database (though it's
slower to restore)... on a busy system, you might not be able to run
multiple full backups during the day, but you can probably run many
transaction log backups during the day.
Second, unless you tell it not to, the backup procedure will delete
all transactions that are already written to the database; this
reduces the amount of your log file that's in use, and keeps it from
growing.
So, once you've backed up the database, you don't need to backup the
transaction log to help restore the database, but you might want to
anyway, to delete the inactive portions.
If you're using the full database recovery model, you'll probably want
to take multiple transaction log backups during the day, both for
database recovery, and to keep the transaction log from filling up.
(Yes, it can autogrow, but it's better if it doesn't.)
--
Everything I needed to know in life I learned in Kindergarten. Like:
Once you pull the pin on Mr. Hand Grenade, he is no longer your friend.
