Microsoft SQL Server 2000 backup and recovery
(Lubor Kollar)
This session gave a great overview of backup and recovery, stressing the all
important point that we perform the ritual of backup for a reason - so that
we can restore. This means that all decisions about backup strategy should
be driven by the restore requirements and these should be carefully thought
out before the backup strategy is implemented. A mismatch between the
implementation and the strategy could be fatal, at least for your career...
There are two basic types of failure:
-
Physical. This can be due to media failure such as disk collapse, or torn
pages where a power failure can result in an 8K page with some old and some
new sectors.
-
Application and/or user error
Data recovery is a (hopefully) rarely practiced art, so it may be time
consuming, and error prone. You can (and should) prevent this by practice
and training. Now is the time to do this, not after a failure has brought
the database to its knees.
Start by planning. Decide exactly what it is that
you want to protect the database from and what recovery time your users want
or demand. This can be anything from 5 seconds to 3 days. People often do
this the wrong way around. They do a back up every day, take logs every once
in a while and then see how long the restore will take.
To improve the speed of restoring, look at the
process as a series of steps and see where the time lies in each step:
-
File creation. This can be time consuming, and may not be necessary; only do
this step if the database doesn't exist!
-
Copying the data and/or the log file to the destination disk. The slowest
part of this process should be the disk I/O. If you find that the network is
actually limiting this, consider fixing that before you have a real failure.
-
Redo. This is performed after the log has been restored. The time taken up
here is, obviously, directly proportional to the log size (so keep it small)
but is also affected by the type and frequency of backup. Also bear in mind
that it will depend upon the number of inserts and updates, but not on the
number of queries. Typically the replay rate is faster than the application
rate by a factor of 10 - 100 but it can, rarely, be slower - so watch out
for that!
-
Undo time. This should be short (just the incomplete transactions) but it
can be significant if the transactions are complex. This can be controlled
by controlling the application.
SQL Server has 3 recovery models:
-
Full. Everything is logged, even the creation of the index. There's no work
loss and it's possible to recover to any point in time.
-
Simple. Less log space required but the greatest work loss is possible.
-
Bulk logged. This is somewhere in between full and simple. It provides high
performance but a minimal log for bulk operations.
The recovery model can be changed using
ALTER DATABASE <name> SET
RECOVERY FULL. The model database determines the recovery model, so
change the model database if you want to alter the default for your future
databases.
The take home message was "use recovery oriented
planning!"
Mark Whitehorn
|
|
