Site: James Rhoat — Giving back to the community
Source: https://james.rhoat.com/p/recovery-model-behavior/
Title: Recovery Model Behavior
Description: A comparison of SQL Server's Full, Simple, and Bulk-Logged recovery models, covering transaction log behavior, backup requirements, and disaster recovery implications.
Date: 2021-01-08
Categories: sql-server, SQL Server recovery model comparison, full vs simple recovery model, bulk logged recovery model behavior, transaction log backup requirements, point-in-time recovery in SQL Server
Word count: 311
Reading time: 2 min

Contents:
- [Full Recovery Model](#full-recovery-model)
- [Simple Recovery Model](#simple-recovery-model)
- [Bulk-Logged Recovery Model](#bulk-logged-recovery-model)

***

# Recovery Model Behavior


## Full Recovery Model

This recovery is the most common recovery model used for SQL Server. This model is required for database mirroring and availability groups.

- Allows log backups and zero-to-minimal data loss after a disaster
- All changes to the database are fully logged to the transaction log file
- The transaction log will not clear data until a log backup has been performed, meaning that the log backup is what clears the transaction log
- This means that log backups are required, otherwise the transaction log will continually grow until your drive is full

## Simple Recovery Model

Commonly used when log backups and point-in-time recovery are not required.

- Some operations can use minimal logging
- All other operations are fully logged, as in the FULL recovery model
- The transaction log will not clear data until a checkpoint has been performed
- Log backups are NOT possible with this recovery model

> **Important:** Disaster recovery is only possible using full and differential backups, so data loss will occur back to the most recent data backup. Simple recovery is not an appropriate choice for production systems where loss of recent changes is unacceptable.

## Bulk-Logged Recovery Model

Commonly used to minimize log growth during bulk operations, while preserving the ability to take log backups.

- Some operations can use minimal logging
- All other operations are fully logged, as in the FULL recovery model
- The transaction log will not clear data until a log backup has been performed, meaning that the log backup is what clears the transaction log
- This means that log backups are required, otherwise the transaction log will continually grow until your drive is full
- The time running in this mode should be minimized to reduce potential loss of data

> **Important:** The Bulk-Logged recovery model should not be used if there is the possibility of losing user transactions.

Microsoft has a [high level overview of recovery model differences](https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server).


---

## Related Posts

- [Volume Shadow Copy Service (VSS)](https://james.rhoat.com/p/vss-backups/page.md) — Jan 15, 2021: How VSS and the SQL Writer Service interact with SQL Server backups, including supported operations, limitations around point-in-time recovery, and common troubleshooting steps.
- [Vertical Partitioning](https://james.rhoat.com/p/vertical-partitioning/page.md) — Jan 14, 2021: How vertical partitioning (row splitting) reduces page waste and improves query performance in SQL Server, with a one-million-row demo.
- [Understanding Storage in SQL Server](https://james.rhoat.com/p/understanding-storage/page.md) — Jan 13, 2021: How SQL Server organizes data at the page and extent level, and why LOB, in-row, and row-overflow storage matter for performance and design.