Overview of the Recovery Models
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 bulk logging most bulk operations. |
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.
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.
See Also
Concepts
Choosing the Recovery Model for a Database
Recovery Models for System Databases
Considerations for Switching from the Full or Bulk-Logged Recovery Model
Considerations for Switching from the Simple Recovery Model
Transaction Log Truncation
Transaction Log Physical Architecture
Other Resources
ALTER DATABASE (Transact-SQL)
Backing Up and Restoring Databases in SQL Server
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
5 December 2005 |
|