Recovery Models (SQL Server)
SQL Server backup and restore operations occur within the context of the recovery model of the database. Recovery models are designed to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time.
In this Topic:
Recovery Model Overview
The following table summarizes the three 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. For information about database backups under the simple recovery model, see Full Database Backups (SQL Server). Operations that require transaction log backups are not supported by the simple recovery model. The following features cannot be used in simple recovery mode: Log shipping AlwaysOn or Database mirroring Media recovery without data loss Point-in-time restores |
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. For more information, see Complete Database Restores (Simple Recovery Model). |
| 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). For information about database backups under the full recovery model, see Full Database Backups (SQL Server) and Complete Database Restores (Full Recovery Model). |
Normally none. If the tail of the log is damaged, changes since the most recent log backup must be redone. |
Can recover to a specific point in time, assuming that your backups are complete up to that point in time. For information about using log backups to restore to the point of failure, see Restore a SQL Server Database to a Point in Time (Full Recovery Model). Note: If you have two or more full-recovery-model databases that must be logically consistent, you may have to implement special procedures to make sure the recoverability of these databases. For more information, see Recovery of Related Databases That Contain Marked Transaction. |
| 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 information about operations that can be minimally logged, see The Transaction Log (SQL Server). For information about database backups under the bulk-logged recovery model, see Full Database Backups (SQL Server) and Complete Database Restores (Full Recovery Model). |
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. |
Related Tasks
See Also
backupset (Transact-SQL)
sys.databases (Transact-SQL)
ALTER DATABASE SET Options (Transact-SQL)
Back Up and Restore of SQL Server Databases
The Transaction Log (SQL Server)
Automated Administration Tasks (SQL Server Agent)
Restore and Recovery Overview (SQL Server)