SQL Server Recovery Models Explained

Understanding SQL Server Recovery Models is fundamental to backup strategy, restore capability, and transaction log management.

A recovery model determines 2 things: what you can restore, and 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 3 models, and the first job is finding out which one you are actually in:

-- Check recovery model for all databases
SELECT 
    name,
    recovery_model_desc
FROM sys.databases
ORDER BY name;

Quick Comparison

The rest of this page is what each row costs in practice, measured on a lab instance.

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 3 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.

Run against a lab instance: the same workload, 20 batches of 2,000 rows inserted and deleted with a CHECKPOINT between each, against one database whose log file started at 64 MB.

Row 2 is the one worth knowing about. A database set to Full that has never had a full backup behaves exactly like the Simple row above it, because there is no restore base for a log backup to extend. Only once the first full backup exists does the log start holding on to everything, and that is row 3: the file tripled and sat at 93.6 percent used with log_reuse_wait_desc reading LOG_BACKUP. One log backup took it to 10.4 percent used with the file still 384 MB, which is why shrinking is the wrong instinct. The space was already free inside the file.

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, once a full backup exists
• 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 3 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. If the log is full right now, work those checks in order rather than reaching for a shrink.

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, BACKUP LOG is refused outright
• 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. Asking for one returns Msg 4208, The statement BACKUP LOG is not allowed while the recovery model is SIMPLE, which is the engine telling you the choice was already made.

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, and switching back does not mend it. 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.

The reduction is not marginal. On the same lab instance, an identical SELECT INTO of 200,000 rows wrote 44.5 MB of transaction log under Full and 1.1 MB under Bulk-Logged. Both produced the same 200,000 rows. The log records for the minimally logged operation are not there to replay, which is exactly why point-in-time restore stops working across that window.

When Bulk-Logged Makes Sense

Use it where restore granularity is temporarily less important than throughput, for example:

• 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

2 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.

Switching back to Full does not repair it. A database taken to Simple and straight back to Full still has no usable restore base, so the next log backup is refused until a new full backup runs.

Make sure that decision is intentional.

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.