SQL Server provides three recovery models that control transaction log maintenance and backup operations. The three recovery models are full, bulk-logged, and simple. Choosing the appropriate recovery model is an important decision that affects performance, backup and restore operations, and data loss scenarios.
What is the full recovery model?
The full recovery model is the most comprehensive recovery model in SQL Server. When using the full recovery model:
- All changes are fully logged in the transaction log.
- All transaction log backups must be taken regularly to truncate the inactive portion of the log.
- Point-in-time recovery is supported, allowing you to recover to any point in time within the log backups.
- Media recovery is supported using log backups taken since the last full or differential backup.
The full recovery model provides the highest level of data recoverability because all changes are logged and all log backups are accessible. This comes at a storage cost since the transaction log can grow very large under heavy write activity. Regular log backups are required to prevent the log from growing out of control.
The full recovery model is most appropriate when high recoverability and/or point-in-time recovery are needed, such as in a production OLTP database. The overhead of taking frequent log backups is usually a fair trade-off for the ability to completely recover the database.
Benefits of the full recovery model
- Complete recoverability of lost or corrupted data pages
- Point-in-time recovery supported within log backup frequency
- Required for AlwaysOn Availability Groups and database mirroring
Drawbacks of the full recovery model
- Increased disk storage needed for transaction logs
- Performance overhead for transaction logging
- Log backups required regularly
What is the bulk-logged recovery model?
The bulk-logged recovery model is an intermediate setting that allows selective transaction logging. When using the bulk-logged recovery model:
- Full logging occurs for all ordinary transactions.
- Minimal logging occurs for minimally logged bulk operations like bulk imports, SELECT INTO, bulk loads, and some indexing operations.
- Transaction log backups are still required but less frequently since the log grows slower.
- Point-in-time recovery is only possible to end of the last full or differential backup.
The bulk-logged model minimizes logging for bulk operations that can easily be replayed if needed. Since less data is logged overall, the storage and performance overheads are mitigated compared to full recovery model.
Bulk-logged recovery is useful in ETL scenarios where very large volumes of data are periodically loaded that only need recoverability to the last full backup. It offers a balance between recoverability and resource usage.
Benefits of the bulk-logged recovery model
- Full recoverability for ordinary transactions
- Minimized logging for bulk operations like large imports
- Reduced storage and I/O overhead compared to full recovery
Drawbacks of the bulk-logged recovery model
- Point-in-time recovery only to end of last full backup
- Bulk operations not protected by log backups
- Not supported for high availability configurations like AlwaysOn AG
What is the simple recovery model?
The simple recovery model is the most basic recovery model in SQL Server. When using the simple recovery model:
- Only minimal logging occurs – bulk operations and ordinary transactions are minimally logged.
- Log truncation happens automatically when checkpoints occur.
- Log backups are not required at all.
- Only full or differential database backups are needed.
- Point-in-time recovery is not possible.
The simple recovery model minimizes logging overhead to increase performance at the cost of recoverability. All activity is minimally logged, resulting in much smaller log storage needs.
The simple model is suitable for development or test databases where high performance is more important than recoverability. It can also be useful for easily reproducible or re-creatable data.
Benefits of the simple recovery model
- Minimal logging for all operations
- No log backups needed
- Least I/O and storage overhead
Drawbacks of the simple recovery model
- No point-in-time recoverability
- Minimal protection against data loss
- Not supported for high availability configurations like AlwaysOn AG
How do the recovery models differ in transaction logging?
The key difference between the three recovery models is the amount and type of transaction logging that occurs:
| Recovery Model | Ordinary Transaction Logging | Bulk Operation Logging |
|---|---|---|
| Full | Fully Logged | Fully Logged |
| Bulk-Logged | Fully Logged | Minimally Logged |
| Simple | Minimally Logged | Minimally Logged |
Full recovery model logs all changes fully, bulk-logged partially logs bulk changes, and simple minimally logs all changes. This affects the recoverability window and resource overhead of maintaining the transaction log.
How do the recovery models differ in log truncation?
The recovery models also differ in when and how transaction logs are truncated (freed of inactive content):
- Full: Logs are only truncated by taking log backups. The log accumulates all activity since the last backup.
- Bulk-Logged: Logs are truncated after log backups occur. Less inactive content accumulates than full recovery.
- Simple: Logs are truncated automatically by checkpoints. Log backups are not taken.
Full recovery requires manual log truncation via log backups to prevent unbounded log growth. Simple recovery truncates automatically when checkpoints occur. Bulk-logged truncates partially after log backups.
How do the recovery models differ in point-in-time recovery support?
The recovery models offer different abilities to restore to a point-in-time, as follows:
- Full: Any point-in-time can be recovered to within the retention period of log backups.
- Bulk-Logged: Only to end of last full or differential backup.
- Simple: No point-in-time recovery.
Full recovery provides the finest point-in-time granularity due to full transaction logging. Simple does not allow point-in-time recovery at all since logging is minimal.
How do the recovery models differ in backup requirements?
The backup requirements also vary considerably between recovery models:
- Full: Full backups + Transaction log backups + Optionally differential backups
- Bulk-Logged: Full backups + Transaction log backups + Optionally differential backups
- Simple: Full backups only + Optionally differential backups
Full and bulk-logged require transaction log backups to augment full/differential backups for recoverability and log truncation. Simple only strictly requires full backups since logging is minimal.
When should you use each recovery model?
Choosing the appropriate recovery model depends on your recoverability, performance and management requirements. Some general guidelines on when to use each model:
- Full: When high recoverability is critical – such as in production OLTP databases. The overhead is usually worth the ability to completely recover and restore to any point-in-time. Required for AlwaysOn Availability Groups.
- Bulk-Logged: For ETL processes or data warehouses that periodically bulk load large amounts of data that can easily be re-loaded if needed. Want some extra recoverability without the full overhead of full logging.
- Simple: For non-critical development/testing systems where performance is more important than recoverability. Also for easily reproducible or re-created data.
Most production systems benefit from full recovery model. But bulk-logged or simple can be suitable in certain scenarios if recoverability needs are lower.
Conclusion
SQL Server’s recovery models provide a useful trade-off between recoverability, performance and manageability. The key points about each model are:
- Full recovery model provides the highest recoverability by fully logging all activity but has the highest overhead.
- Bulk-logged recovery model minimizes logging of bulk operations to reduce overhead compared to full recovery. Recoverability is reduced.
- Simple recovery model minimally logs all activity to optimize performance but provides no detailed recoverability.
Choosing the right recovery model requires understanding your requirements for recoverability vs. performance/management. For most production databases, full recovery is recommended. Bulk-logged can serve a purpose for periodic large bulk operations that only need recovery to a backup point. Simple recovery is best for non-critical development/testing systems.