SQL Server Recovery Models

Before we talk about the recovery models lets jump 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 roll.

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 all 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 model 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 model with a few benefits and tradeoffs. Like the full recovery model, 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.