Understanding SQL Server Recovery Models is fundamental to backup strategy, restore capability, and transaction log management.
A recovery model determines two things:
- What you can restore
- How the transaction log behaves
Get it wrong and you either lose restore precision or end up chasing log growth incidents.
Every database runs under one of three models:
- Full
- Simple
- Bulk-Logged
Start by checking what you’re running:
-- Check recovery model for all databases
SELECT
name,
recovery_model_desc
FROM sys.databases
ORDER BY name;What Recovery Model Actually Changes
A recovery model affects:
- Whether point-in-time restore is possible
- Whether transaction log backups are required
- When log space can be reused
All three are consequences of how the transaction log is handled. That’s the entire conversation.
Everything else people associate with recovery models usually traces back to one of those behaviours. If you understand how the log behaves, you understand recovery models.
Full Recovery Model
Full is the default for production systems where data loss is not acceptable.
In Full recovery:
- Transaction log backups are required
- Point-in-time restore is supported
- The log does not truncate on checkpoint
- Log space is only reused after a successful log backup
This is where most log growth incidents originate.
If a database is in Full and the log is growing, check three things first:
- Are log backups running?
- Are they succeeding?
- Is a long-running transaction preventing truncation?
Shrinking the log does not solve the problem. Missing or failing log backups usually do.
Full recovery gives you restore precision, but only if log backups run consistently and the chain remains intact.
Simple Recovery Model
Simple is not weaker. It is a deliberate trade-off.
In Simple recovery:
- Transaction log backups are not possible
- Point-in-time restore is not possible
- Log space can be reused automatically after checkpoint
Restore options are limited to:
- The last full backup
- Plus the last differential (if taken)
There is no replaying log backups to land at 10:37 before a bad change.
Simple makes sense where rebuild is acceptable:
- Dev and test
- Staging environments
- Reporting copies
- Data processing systems where the upstream source is authoritative
One detail that often gets overlooked: Switching from Full to Simple breaks the log chain. From that point forward, you are intentionally giving up log-based recovery. Make sure that is a business decision, not an accident.
Bulk-Logged Recovery Model
Bulk-Logged sits between Full and Simple, but it is rarely a permanent choice.
It is a temporary setting for heavy bulk activity.
In Bulk-Logged:
- Log backups still run
- Certain bulk operations are minimally logged
- Point-in-time restore may not be possible across log backups containing minimally logged changes
That trade-off is the entire point. You reduce log volume. You reduce restore precision during that window.
When Bulk-Logged Makes Sense:
Use it where restore granularity is temporarily less important than throughput.
Examples:
- Large staging loads or backfills where data can be regenerated
- Warehouse-style batch processing (SELECT INTO, large rebuilds)
- Planned maintenance windows with predictable heavy logging
If you use it:
- Time-box it
- Document it
- Switch back deliberately
Transaction Log Backups Define Your RPO
In Full or Bulk-Logged recovery, log backup frequency effectively defines your recovery point objective.
Common schedules:
- Every 15 minutes
- Every 5 minutes for higher change systems
- Every 30 minutes for quieter workloads
- Hourly only where the business accepts that exposure
If you do not have synchronous HA protecting you, your log backup frequency is your data loss exposure.
If log backups fail silently, your exposure is unknown. That is usually worse than “high”.
Changing Recovery Model
ALTER DATABASE [YourDatabase]
SET RECOVERY FULL; -- SIMPLE / BULK_LOGGED
Two things people forget:
Simple → Full
You must take a new full backup before log backups become usable.
Full → Simple
You are deliberately breaking the log chain and removing point-in-time restore capability.
Make sure that decision is intentional.
Quick Comparison
| Feature | Full | Simple | Bulk-Logged |
|---|---|---|---|
| Log backups | Yes | No | Yes |
| Point-in-time restore | Yes | No | Limited during bulk operations |
| Automatic log reuse | No | Yes | No |
| Typical use | Production OLTP | Rebuildable / non-critical | Time-boxed bulk work |
How I Set It Across Environments
- Production OLTP: Full + reliable log backups
- Dev / test / rebuildable data stores: Simple
- Bulk processing windows: Temporary Bulk-Logged only when justified
Recovery model is not a cosmetic setting.
It determines what options you have when something goes wrong.
If you want the ability to restore to just before the change, you need Full recovery and log backups running consistently.
Leave a Reply