Choosing a SQL Server Recovery Model: FULL vs SIMPLE vs BULK_LOGGED for Production OLTP

If you run a production OLTP database on SQL Server, the recovery model decision determines two things that matter when something goes wrong: how much data you can lose, and whether you can restore to an exact moment before the failure. The short version: use FULL for any production database where losing transactions since the last full or differential backup is unacceptable, pair it with scheduled log backups, and validate point-in-time restore before you need it. Everything else in this article is the reasoning and the mechanics behind that recommendation.

The decision and its constraints

SQL Server offers three recovery models per database: SIMPLE, FULL, and BULK_LOGGED. The model controls how transactions are recorded in the transaction log and whether the log can be backed up. Your constraints are:

• Recovery point objective (RPO): how many minutes of committed transactions the business tolerates losing.
• Point-in-time restore: whether you need to recover to a specific moment, for example just before a bad deployment or accidental DELETE.
• Operational capacity: whether someone will own log backup jobs, log disk monitoring, and restore testing.
• Feature dependencies: Always On availability groups and log shipping require FULL recovery. Switching to SIMPLE breaks them.

Comparing the three models

Model	Log backups	Point-in-time restore	Data-loss exposure	Admin overhead	Typical fit
FULL	Required and supported	Yes	Bounded by log backup frequency	High: log backup jobs, log space monitoring	Production OLTP, anything with a real RPO
SIMPLE	Not supported	No	Everything since the last full or differential backup	Minimal: log truncates on checkpoint	Dev, rebuildable data, reporting copies
BULK_LOGGED	Supported	Not for log backups containing minimally logged operations	Similar to FULL, but weakened during bulk operations	High, plus careful scoping of bulk windows	Temporary switch during index rebuilds or bulk loads

Trade-offs in practice

FULL: the production default, with a failure mode

FULL records every transaction and keeps log records until a log backup truncates the log. This is what enables point-in-time restore and keeps your data-loss window small. The catch is the most common operational failure in this area: a team switches a database to FULL and never sets up log backups. The transaction log then grows unbounded — log records are never truncated — until the disk fills and the database halts. FULL without log backups is strictly worse than SIMPLE for operability.

Log backup frequency is an explicit engineering decision, not a default. Backing up the log every 5–15 minutes sets your RPO to roughly that interval (plus restore time). Confirm this number with stakeholders; it is a business commitment expressed as a job schedule.

SIMPLE: honest about its exposure

SIMPLE truncates the log on checkpoint, so log files stay small and there is no log backup administration at all. The price is that recovery ends at the most recent full or differential backup. If you take nightly fulls, your exposure window is up to 24 hours. That is fine for development databases, ETL staging that can be re-run, or reporting copies rebuilt from a source of truth. It is rarely fine for a system of record.

BULK_LOGGED: a scoped tool, not a steady state

BULK_LOGGED minimally logs certain bulk operations (bulk inserts, index rebuilds, SELECT INTO), which sharply reduces log volume during large maintenance work. The trade-off: a log backup that contains minimally logged operations cannot be used for point-in-time restore — you can only restore that log backup to its end. The supported pattern is to switch to BULK_LOGGED for a defined maintenance window, complete the bulk work, take a log backup, and switch back to FULL. Leaving a database in BULK_LOGGED permanently quietly weakens your recovery guarantee.

Implementation: enabling FULL with a working backup chain

Run these in SQL Server Management Studio or Azure Data Studio, connected as a login with ALTER permission on the database (sysadmin or db_owner works). Replace YourDb and the backup paths with real values.

-- 1. Check the current model
SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'YourDb';

-- 2. Switch to FULL
ALTER DATABASE YourDb SET RECOVERY FULL;

-- 3. Take a full backup immediately — required before log backups are possible
BACKUP DATABASE YourDb TO DISK = 'D:\Backups\YourDb_full.bak';

-- 4. Schedule recurring log backups (via SQL Server Agent job or maintenance plan)
BACKUP LOG YourDb TO DISK = 'D:\Backups\YourDb_log.trn';

Step 3 is the one people miss: switching from SIMPLE to FULL does not take effect for log backup purposes until you take a full (or differential) backup afterward. Until then, the database behaves as if it were still in SIMPLE (sometimes called pseudo-SIMPLE). If you schedule log backups without this step, the log backup job will fail.

Validation: prove point-in-time restore before you rely on it

A backup strategy that has never been restored is a hypothesis. On a non-production server or instance, test the full chain:

-- Restore the full backup, leaving the database ready for more restores
RESTORE DATABASE YourDb_PIT_Test
FROM DISK = 'D:\Backups\YourDb_full.bak'
WITH NORECOVERY, MOVE ... ; -- map files to test paths

-- Apply log backups up to a target time
RESTORE LOG YourDb_PIT_Test
FROM DISK = 'D:\Backups\YourDb_log.trn'
WITH STOPAT = '2026-10-12T13:00:00', RECOVERY;

Confirm the restored database contains the data state you expect at the STOPAT time. Then verify ongoing log truncation in production with DBCC SQLPERF(LOGSPACE) or sys.dm_db_log_space_usage: log used percentage should stay bounded and drop after each log backup. If used space climbs monotonically, log backups are not running or are failing — investigate before the disk fills.

Limitations and notes

• Behavior around which operations are minimally logged, and restore options for those backups, varies by SQL Server version and edition. Confirm against the documentation for your version before relying on BULK_LOGGED.
• Point-in-time restore depends on an unbroken log backup chain. A missing or corrupted log backup breaks STOPAT recovery past that point; protect and retain the chain accordingly.
• Log backups add I/O and storage cost. On very high-throughput systems, size the log backup destination and frequency together.

The decision reduces to one question: can you afford to lose everything since the last full backup? If not, FULL plus tested log backups is the only supported answer.