powershelldba.de · Uwe Janke

SQL Server Recovery Models: SIMPLE, FULL and BULK_LOGGED Compared

Three settings, one real decision: how much data are you willing to lose. Most of the confusion around recovery models disappears once you stop thinking of them as backup settings and start thinking of them as log retention settings.

The one-sentence answer

The recovery model controls when transaction log records become reusable. That is the whole mechanism. Every advantage and every disadvantage below is a consequence of that one rule.

Notice that none of those descriptions mention backups being taken or not taken. The recovery model does not take backups. It decides what your backups are capable of.

Why "recovery model" is a misleading name

The name suggests a recovery strategy. It is really a log retention policy. A database in FULL recovery with no log backups scheduled is not safer than one in SIMPLE. It is strictly worse: you get the same data loss exposure, plus a transaction log that grows until the disk fills.

This is the single most common recovery model mistake in the field. Somebody reads that FULL is the "safe" one, sets every database to FULL, schedules a nightly full backup, and goes home. Three weeks later the log file is 400 GB and the application is down with a log-full error. The recovery model was never the protection. The log backup schedule was.

A recovery model is a promise about what you could restore. Only the backup schedule makes that promise real.

Side-by-side comparison

Property SIMPLE FULL BULK_LOGGED
Log truncation Automatic, at checkpoint Only after a log backup Only after a log backup
Log backups Not possible Required Required
Point-in-time restore No Yes Not across minimally logged operations
Tail-log backup No Yes Only if no minimally logged operations since the last log backup
Worst-case data loss Everything since the last full or differential backup Everything since the last log backup, often seconds Everything since the last log backup
Bulk operations Minimally logged Fully logged Minimally logged
Works in an Availability Group No Yes No
Works with log shipping No Yes Yes, with the caveats below
Admin effort Almost none Real, ongoing Real, plus a switching discipline

SIMPLE

At every checkpoint, SQL Server marks the inactive part of the log as reusable. Nothing is retained for backup purposes. The log file stays roughly the size of your largest concurrent transaction, and it manages itself.

Advantages

Disadvantages

When SIMPLE is the right call

When the data can be reconstructed from somewhere else, or when losing a day of it costs less than running a log backup chain. In practice:

The mistake worth avoiding "It's only a reporting database" is not the same as "the data is reproducible". Plenty of reporting databases accumulate hand-maintained mappings, overrides and business rules that exist nowhere else. Before you choose SIMPLE, ask who could rebuild the contents and how long it would take.

FULL

Every operation is fully logged, and log records stay in the log until a log backup copies them out. That retained chain is what makes point-in-time restore possible: restore the last full backup, then the differentials, then replay log backups up to a chosen second.

Advantages

Disadvantages

When FULL is the right call

Any database holding data that cannot be reconstructed, which in most shops means every production database that users type into. Also any database in an Availability Group, and anything under a regulatory retention or auditability requirement, where "we lost a day" is not an acceptable sentence in an incident report.

Sizing the log backup interval Work backwards from the RPO the business actually agreed to, not from a habit. If the agreed tolerance is 15 minutes, log backups every 15 minutes satisfy it and anything more frequent is optional. If nobody has ever stated an RPO, that conversation is more valuable than any setting in this article. A practical starting point is covered in the backup jobs admin guide.

BULK_LOGGED

BULK_LOGGED behaves like FULL, with one exception: bulk operations are minimally logged. Instead of logging every row, SQL Server logs the extent allocations and flags the changed extents. Typical candidates are BULK INSERT, bcp, SELECT ... INTO, CREATE INDEX and ALTER INDEX ... REBUILD.

The effect on a big load is dramatic: an operation that would write tens of gigabytes of log in FULL can write a small fraction of that.

Advantages

Disadvantages

These are the reason BULK_LOGGED is a temporary switch and not a setting you leave on:

When BULK_LOGGED is the right call

As a deliberate, bounded switch around a known bulk operation, in a maintenance window, on a database that is otherwise in FULL. The discipline that makes it safe:

-- 1. Close the chain cleanly before switching
BACKUP LOG [MyDb] TO DISK = 'L:\Backup\MyDb_pre_bulk.trn';

-- 2. Switch
ALTER DATABASE [MyDb] SET RECOVERY BULK_LOGGED;

-- 3. Run the bulk operation here

-- 4. Switch straight back
ALTER DATABASE [MyDb] SET RECOVERY FULL;

-- 5. Immediately re-establish point-in-time capability
BACKUP LOG [MyDb] TO DISK = 'L:\Backup\MyDb_post_bulk.trn';

Steps 1 and 5 are the point of the whole exercise. They confine the "no point-in-time restore" gap to the bulk window itself, instead of letting it contaminate an arbitrary stretch of your log chain.

If you cannot tolerate the gap at all Then do not use BULK_LOGGED. Keep FULL, size the log for the operation, and batch the work instead. Batched deletion and controlling log growth during an ALTER COLUMN cover that approach. A slower load with an intact restore chain is usually the better trade in a regulated environment.

The pseudo-SIMPLE trap

This one deserves its own section because it silently defeats an otherwise correct configuration.

A database set to FULL recovery that has never had a full backup taken does not behave as FULL. It keeps truncating its log at checkpoint, exactly like SIMPLE. There is no log chain, because a log chain has to start somewhere, and it starts at the first full backup.

So a freshly created database, set to FULL on day one, with log backups dutifully scheduled, is not protected at all until that first full backup runs. Every log backup before it fails. The setting says FULL. The behaviour is SIMPLE. This state is commonly called pseudo-SIMPLE.

You can see it directly:

SELECT d.name,
       d.recovery_model_desc,
       d.log_reuse_wait_desc,
       drs.last_log_backup_lsn
FROM   sys.databases d
JOIN   sys.database_recovery_status drs
       ON drs.database_id = d.database_id
WHERE  d.recovery_model_desc <> 'SIMPLE';

A last_log_backup_lsn of NULL on a database that claims to be in FULL recovery means no log chain exists yet. Take a full backup and it starts.

Switching between models

Switching is not symmetric, and the asymmetry matters.

Switch Effect on the log chain What you must do
FULL to BULK_LOGGED Chain survives Take a log backup first, so the pre-bulk state is recoverable
BULK_LOGGED to FULL Chain survives Take a log backup immediately, to restore point-in-time capability
FULL to SIMPLE Chain is broken Take a log backup before switching, or lose the ability to recover that window
SIMPLE to FULL No chain until the next full or differential backup Take a full or differential backup immediately, otherwise you are in pseudo-SIMPLE

The trap is the round trip. Somebody switches a database to SIMPLE to deal with a full log, the incident calms down, and they switch it back to FULL and consider the matter closed. It is not closed. Until a full or differential backup runs, that database is unprotected, and the log backup job may be failing quietly in the background. Switching to SIMPLE to fix a full log is almost always the wrong move anyway; see log truncation vs. log shrinking for what to do instead.

Finding out what you actually have

Inventory first, opinions second. This lists every database with its model, why its log cannot be reused right now, and how long since its last full and log backup:

SELECT d.name,
       d.recovery_model_desc,
       d.log_reuse_wait_desc,
       MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS last_full,
       MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS last_log
FROM   sys.databases d
LEFT   JOIN msdb.dbo.backupset b
       ON b.database_name = d.name
WHERE  d.database_id > 4
GROUP  BY d.name, d.recovery_model_desc, d.log_reuse_wait_desc
ORDER  BY d.recovery_model_desc, d.name;

Two patterns in that output are worth acting on immediately:

Why is database_id > 4 in that query? It skips the system databases. master, msdb and model have their own rules, and tempdb is always SIMPLE and cannot be changed. Note that model is worth checking separately: its recovery model is inherited by every database created afterwards, so a wrong setting there quietly propagates.

Choosing, in practice

Three questions, in order. They resolve most cases without debate.

  1. If this database was lost, could somebody rebuild its contents from another system? If yes, and doing so is genuinely routine rather than theoretical, SIMPLE is defensible.
  2. Is it in an Availability Group, mirrored, or log shipped, or might it be within a year? If yes, FULL. There is no decision to make.
  3. How much data is the business willing to lose? If the honest answer is less than a day, you need FULL and a log backup interval that matches the number. If nobody can answer, that is the real finding, and it belongs in the risk register rather than in a database setting.

BULK_LOGGED is not in that list on purpose. It is not a steady-state answer to "which model should this database use". It is a tool you reach for during a specific operation and put down afterwards.

Related reading

The short version

Pick SIMPLE when the data is reproducible and you would rather not run a log chain. Pick FULL when it is not, and then actually schedule the log backups, because the setting alone protects nothing. Use BULK_LOGGED as a switch around a bulk operation, with a log backup on each side of it, and switch back when you are done.

And check for pseudo-SIMPLE. A database that says FULL and has never had a full backup is not protected, and it will not tell you so.

← Back to Blog