Delen via

  • Artikel

Recovery Model Overview

Recovery models are designed to control transaction log maintenance. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model.

The following table summarizes these recovery models.

Recovery model

Description

Work loss exposure

Recover to point in time?

Simple

No log backups.

Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space.

Changes since the most recent backup are unprotected. In the event of a disaster, those changes must be redone.

Can recover only to the end of a backup.

Full

Requires log backups.

No work is lost due to a lost or damaged data file.

Can recover to an arbitrary point in time (for example, prior to application or user error).

Normally none.

If the tail of the log is damaged, changes since the most recent log backup must be redone. For more information, see Tail-Log Backups.

Can recover to a specific point in time, assuming that your backups are complete up to that point in time. For more information, see Restoring a Database to a Point Within a Backup.

Bulk logged

Requires log backups.

An adjunct of the full recovery model that permits high-performance bulk copy operations.

Reduces log space usage by using minimal logging for most bulk operations. For more information, see Operations That Can Be Minimally Logged.

If the log is damaged or bulk-logged operations occurred since the most recent log backup, changes since that last backup must be redone.

Otherwise, no work is lost.

Can recover to the end of any backup. Point-in-time recovery is not supported.

Note

The appropriate recovery model for a database depends on the availability and recovery requirements of the database. For more information about these requirements, see Choosing the Recovery Model for a Database.

Simple Recovery Model

The simple recovery model minimizes administrative overhead for the transaction log, because the transaction log is not backed up. The simple recovery model risks significant work-loss exposure if the database is damaged. Data is recoverable only to the most recent backup of the lost data. Therefore, under the simple recovery model, the backup intervals should be short enough to prevent the loss of significant amounts of data. However, the intervals should be long enough to keep the backup overhead from affecting production work. Including differential backups in the backup strategy can help reduce the overhead.

Generally, for a user database, the simple recovery model is useful for test and development databases or for databases containing mostly read-only data, such as a data warehouse. The simple recovery model is inappropriate for production systems where loss of recent changes is unacceptable. In such cases, we recommend using the full recovery model.

For more information, see Backup Under the Simple Recovery Model.

Full Recovery and Bulk-Logged Recovery Models

The full recovery and bulk-logged recovery models provide greater protection for data than the simple recovery model. These recovery models rely on backing up the transaction log to provide full recoverability and to prevent work loss in the broadest range of failure scenarios.

  • Full recovery model

    Provides the normal database maintenance model for databases where durability of transactions is necessary.

    Log backups are required. This model fully logs all transactions and retains the transaction log records until after they are backed up. The full recovery model allows a database to be recovered to the point of failure, assuming that the tail of the log can be backed up after the failure. The full recovery model also supports restoring individual data pages.

    For more information, see Backup Under the Full Recovery Model.

  • Bulk-logged recovery model

    This recovery model bulk logs most bulk operations. It is intended solely as an adjunct to the full recovery model. For certain large-scale bulk operations such as bulk import or index creation, switching temporarily to the bulk-logged recovery model increases performance and reduces log space consumption. Log backups are still required. Like the full recovery model, the bulk-logged recovery model retains transaction log records until after they are backed up. The tradeoffs are bigger log backups and increased work-loss exposure because the bulk-logged recovery model does not support point-in-time recovery. For more information, see Backup Under the Bulk-Logged Recovery Model and Considerations for Switching from the Full or Bulk-Logged Recovery Model.

Important

Under the full recovery and bulk-logged recovery models, log backups are essential. If you do not want to take log backups, use the simple recovery model.