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.
