The recovery model of a database is a critical setting that determines how the transaction log both records and retains information about transactions that occur in the database. It’s important to remember that the setting always applies to the entire database.

As we’ll explore in this post, there are 3 recovery models in SQL Server to choose from: FULL, BULK_LOGGED, and SIMPLE; each model has advantages, disadvantages, and tradeoffs. Only you can decide which model is appropriate for your particular situation and goals; we’ll go through some of those tradeoffs and a few typical scenarios.

First of all, let’s take a quick step back and ask why we care about what gets recorded in the transaction log.

What is recorded in the transaction log?

The transaction log contains an ordered sequence of physical operations that occur within a database. Each physical operation is written to the transaction log before the change itself is applied to the data page(s). This is called write-ahead logging, and it occurs regardless of the recovery model chosen.

Usually, the physical operations written to the transaction log also contain enough information to perform the inverse operation of the change (i.e., to undo a delete, do an insert). This combined with the write-ahead logging mechanism ensures transactions succeed or fail as a whole (atomicity) by giving SQL Server the ability to both undo and redo transactions. Hopefully those two bolded words set off hints about how logging is intimately tied to backup, restore, and several other features in SQL Server — we’ll get to those in a minute.

Minimal Logging

There are a handful of operations we can do in a database that can be minimally logged — this means that only enough information required to undo the transaction is logged. In the case of a BULK INSERT, for example, all that’s logged are page allocations — we can undo those by deallocating the pages, but we can’t redo them because the actual contents of the pages aren’t recorded in the log.

We gain speed by writing to the transaction log less, but lose the ability to perform point-in-time recovery during the operation.

You might be wondering how a database restored using only transaction log backups can be complete after a point in time where an operation such as a BULK INSERT took place. How does SQL Server know what to put on those data pages, since only the allocations were logged?

The answer is in what is recorded by the first transaction log backup taken after the minimally-logged operation. SQL Server keeps track of extents that are modified by minimally-logged operations in a special metadata page (the ML map, or minimally-logged map, which contains 1 bit per extent), and copies those extents into the transaction log backup, along with the required portion of the transaction log itself. Yes, transaction log backups can contain data!

The net result of minimal logging isn’t that we get the operation completely for free; we just defer a good chunk of the work to the backup subsystem for an immediate gain in speed.

Armed with this knowledge, let’s take a look at the features of each of the three recovery models.

FULL

• Full logging, all the time.
• Supports point-in-time recovery (you must be taking transaction log backups).
• Only transaction log backups internally clear the transaction log, so a good backup strategy is a must.
• Required for certain features, such as Database Mirroring and Availability Replicas.
• Typically used for OLTP databases, and where data protection is a high priority.

BULK_LOGGED

• Full logging, except when the required conditions are met for minimally-logged operations.
• Supports point-in-time recovery (again, you have to take transaction log backups) for all transaction log backups that do not contain minimally-logged operations. This means you should take a transaction log backup immediately before, and immediately after the minimally-logged operation(s), to maximize your ability to do a point-in-time restore.
• Only transaction log backups internally clear the transaction log (same as FULL).
• Used for performing bulk operations on a database that would otherwise prefer to be in FULL recovery. A database can be switched freely between FULL and BULK_LOGGED without breaking the transaction log backup chain.

SIMPLE

• Full logging, except when the required conditions are met for minimally-logged operations.
• Does not support point-in-time recovery, and the transaction log cannot be backed up.
• A database CHECKPOINT internally clears the transaction log as part of the process (since you can’t take transaction log backups), which means you don’t necessarily have to manage the transaction log as much as the other recovery models.
• Usually used for databases that frequently handle large quantities of data (i.e., data warehouses), or databases where point-in-time recovery is not important.
• Switching a database from FULL or BULK_LOGGED to SIMPLE breaks the transaction log backup chain. The transaction log backup chain can be restarted by switching from SIMPLE back to FULL or BULK_LOGGED, and taking a full or differential backup.

 

A good place to start choosing a recovery model would be by answering the following questions. Remember that these are only guidelines, and your specific situation will determine the best course of action.

• Does the database require point-in-time recovery? (Yes = FULL or BULK_LOGGED, No = SIMPLE)
• Are special features going be used on this database? (Yes = FULL or BULK_LOGGED — usually FULL — depending on the feature)
• Will large quantities of data be imported into, or manipulated within this database? (Yes = BULK_LOGGED or SIMPLE)