Database Recovery Models
Before jumping into a
discussion on recovery models we need to first take a look back to our
discussion of transaction logs from last week. Remember
that all modifications made to the database are recorded in
the transaction log. In the event of a failure (such as a
power outage or blue screen) the transaction log can be
used to reapply the changes to the database. Additionally,
checkpoints are used to write all pages in memory back to
the hard disk, lowering the time needed to recover the
database. So once a checkpoint occurs and all the data
pages are written to disk, why do we need to store
information about transactions? This is where the recovery
model plays a role.
Each
database running on a SQL Server can have one of three
recovery models: Full, Bulk_Logged, and
Simple.
In the full recovery model, every change
made to the database is logged -- all UPDATE, DELETE, and
INSERT statements are logged. Additionally, certain
bulk operations, such as BULK INSERT, that are used to make many
modifications quickly are also logged in their entirety (i.e.
each individual row added by the BULK INSERT command would
be logged).
The full recovery model provides the most options in the
event a data file is corrupted.
When a transaction is logged and the database is in full
recovery mode, the transaction stays in the log until it is
backed up. Once
the database is backed up, the space from the old
transactions are freed and can then be used to log new
transactions. Because all transactions are backed up, Full
database backups make it possible to restore a backup to a
"point in time" by applying only the transactions up to
that point. For example, we could restore a full backup and
then restore all our log backups up to a certain point
before data was deleted.
If full
recovery mode tracks all changes made to
the database and allows us to restore transactions to a
point in time, why not just use it all the time? Because
all operations are logged in their entirety,
you could
end up with some big log files. Also, commands like
BULK INSERT will be slowed down because
every
modification must be logged.
The bulk_logged recovery model is a lot like
the full recovery mode with a few benefits and tradeoffs.
Like the full recovery mode, the bulk_logged recovery model
also logs all UPDATE, DELETE, and INSERT statements.
However, bulk_logged only records that the
operation
took place for certain commands. These commands include
BULK INSERT, bcp, CREATE INDEX, SELECT
INTO, WRITETEXT, and UPDATETEXT. The bulk_logged recovery
model is also like the full recovery model in that they do
not reuse (or overwrite) log space until the transactions
have been backed up.
Unlike the full
recovery
model,
if a transaction log includes bulk operations you can not
restore that log to a point in time, you must restore to
the end of the log. Also, a log backup of the database may
be much larger because in the
bulk_logged recovery model, log backups
must copy
all the extents that have changed.
The benefit of the
bulk_logged model is that the log file(s)
for the database can be smaller if you use many bulk
operations. Also, bulk operations are much faster because
only the fact that the operation occurred needs to be
recorded, not every modification to the database.
The last type of recovery
model is the
simple recovery model. Unlike the full and
bulk_logged
recovery models, simple recovery does not backup
transaction logs. In this mode transaction logs are
frequently truncated (truncation is the process of
removing old transactions from the log) automatically. The
simple recovery model can use full and differential
backups.
In SQL Server Enterprise Edition the model database is set
to the full recovery mode. Because all our databases are
basically a copy of the model database, the recovery
model for the databases we create is also full. You can
change the
model database's
recovery model to
another type if you want all new databases you create to
start with another recovery model.
To change
the recovery
model for a database in Enterprise Manager, right click the
database, select properties, and then choose the options
tab. You can also use the ALTER DATABASE statement to
change the recovery model.
One last topic in the database recovery models section is
the change from one model to another. Unlike previous
versions of SQL server, SQL Server 2000 can switch between
full and
bulk_logged recovery models as
needed. For example if you perform bulk operations
infrequently, you can use the full recovery model and
switch to
bulk_logged as needed to have the bulk
operations perform faster. The log backup will be larger
and take longer, however, when you do this.
Switching between the simple
recovery model and another recovery
model is not so "simple." In
order to have the change take place you will need to make a
full backup of your database. You should only use the
simple recovery model for
development databases -- production databases should use the
full or
bulk_logged recovery models.
Page 4: Making a Backup
»
See All Articles by Columnist Michael Aubert
» 
Comment and Contribute
