SQL Server Fundamentals

SQL Server Recovery Models Explained — Simple, Full & Bulk-Logged

By Data Repair Pro Team · July 2026 · 8 min read

One of the most misunderstood settings in SQL Server is the recovery model. It controls how the transaction log behaves, which directly determines: what backup types you can take, whether point-in-time restore is possible, and how large your .ldf file will grow.

Choosing the wrong recovery model is one of the top causes of transaction log full errors and unexpected data loss. This guide explains all three recovery models clearly, with code examples and a decision table to help you pick the right one.

What Is a Recovery Model?

A recovery model is a database-level property that governs:

How transactions are recorded in the transaction log (.ldf)
When the transaction log is truncated (freed up for reuse)
What restore operations are possible (point-in-time vs. latest backup only)

SQL Server has three recovery models: Full, Simple, and Bulk-Logged. Every user database has exactly one recovery model at any time.

Check Your Current Recovery Model

Before changing anything, check what model your databases are currently using:

SELECT name, recovery_model_desc

FROM sys.databases

ORDER BY name;

You can also see it in SSMS: right-click the database → Properties → Options → Recovery model.

1. Full Recovery Model

FULL

Maximum protection. Requires regular log backups.

In the Full recovery model, every transaction is fully recorded in the transaction log. The log is never automatically truncated — it only gets truncated when you take a transaction log backup. This means you can restore your database to any point in time, down to the second, as long as you have an unbroken chain of log backups.

This is the required model for any production database where data loss is unacceptable.

Pros

Point-in-time recovery — restore to any moment
Minimal data loss (can recover up to the last log backup)
Required for log shipping and some Always On configurations
Supports online index operations

Cons

Transaction log grows continuously until a log backup is taken
If log backups are not scheduled, the log will fill up → Error 9002
Requires more disk space for log files

⚠️ Common mistake: Switching a database to Full recovery model but forgetting to schedule log backups. The log will grow indefinitely until the disk is full. Always pair Full recovery model with a SQL Agent job to take log backups every 15–60 minutes.

2. Simple Recovery Model

SIMPLE

Automatic log truncation. No point-in-time restore.

In the Simple recovery model, SQL Server automatically truncates the transaction log at each checkpoint — the portion of the log that has already been written to disk is immediately freed for reuse. This keeps the log file small without any manual intervention.

The trade-off: you can only restore to your last full or differential backup. Any data written after that backup is permanently lost if you need to restore. There are no transaction log backups in Simple mode — they are not possible.

Pros

Transaction log stays small automatically — no log full errors
Zero maintenance overhead for log management
Perfect for development, test, and reporting databases

Cons

Cannot restore to a specific point in time
Data loss between the last backup and the failure point
Cannot use log shipping or database mirroring

💡 Best used for: Development environments, QA/test databases, data warehouses that are rebuilt nightly, read-only reporting databases, and any system where losing a few hours of data is acceptable.

3. Bulk-Logged Recovery Model

BULK-LOGGED

Hybrid model for bulk operations. Use temporarily.

Bulk-Logged is a hybrid between Full and Simple. It behaves like Full recovery for most transactions, but minimally logs bulk operations such as:

BULK INSERT
SELECT INTO
CREATE INDEX / ALTER INDEX REBUILD
bcp (bulk copy program)

Minimally logging these operations uses far less log space, which dramatically speeds up large data loads and index rebuilds. However, it reduces your restore granularity — if a failure occurs during a bulk operation, you can only restore to the start of that bulk operation, not to a specific moment within it.

Best Practice

Switch to Bulk-Logged only during a specific large operation, then switch back to Full immediately afterward. Never leave production databases in Bulk-Logged permanently.

-- Switch to Bulk-Logged for large index rebuild

ALTER DATABASE [MyDatabase] SET RECOVERY BULK_LOGGED;

GO

-- Perform the large operation

ALTER INDEX ALL ON dbo.LargeTable REBUILD;

GO

-- Switch back to Full immediately

ALTER DATABASE [MyDatabase] SET RECOVERY FULL;

GO

-- Take a log backup to resume the log chain

BACKUP LOG [MyDatabase] TO DISK = 'D:\Backups\MyDatabase_log.bak';

Side-by-Side Comparison

Feature	Simple	Full	Bulk-Logged
Log truncation	Automatic at checkpoint	Only after log backup	Only after log backup
Point-in-time restore	✗ No	✓ Yes	Partial only
Transaction log backups	✗ Not possible	✓ Required	✓ Required
Log size control	✓ Automatic	✗ Manual (backups)	✗ Manual (backups)
Bulk ops log size	Minimal	Full size	Minimal
Log shipping support	✗ No	✓ Yes	Limited
Recommended for	Dev / Test / DW	Production	Bulk load windows

How to Change the Recovery Model

You can change the recovery model at any time using T-SQL:

-- Switch to Full

ALTER DATABASE [MyDatabase] SET RECOVERY FULL;

-- Switch to Simple

ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE;

-- Switch to Bulk-Logged

ALTER DATABASE [MyDatabase] SET RECOVERY BULK_LOGGED;

⚠️ Important: When you switch from Simple to Full, take a full backup immediately. Until a full backup is taken after switching, SQL Server behaves as if in Simple mode — it will not start a log chain or allow log backups.

Recovery Model and the Transaction Log

The most common support question we see is: "Why is my .ldf file 50 GB when my .mdf is only 2 GB?"

The answer is almost always: Full recovery model with no log backups configured. The log grows to record every transaction but is never truncated because no log backup has been taken to complete the log chain.

The fix: either schedule regular log backups (correct approach for production), or switch to Simple (acceptable for non-critical databases). Then shrink the log file:

-- After log backup or switch to Simple, shrink the log file

USE [MyDatabase];

DBCC SHRINKFILE (MyDatabase_log, 1);

See our full guide: Fix SQL Server Transaction Log Full — Error 9002.

Which Recovery Model Should You Choose?

Use this simple decision guide:

🏭 Production database with critical data? → Use Full. Schedule log backups every 15–60 minutes.
🧪 Development or test database? → Use Simple. No maintenance needed.
📊 Data warehouse rebuilt nightly? → Use Simple. Log backups add no value here.
🔄 About to load millions of rows or rebuild all indexes? → Switch to Bulk-Logged temporarily, then switch back to Full.
⚙️ Using log shipping or Always On? → Must use Full.

Manage Backups the Easy Way with Data Repair Pro

Data Repair Pro includes a built-in SQL Server backup and restore interface — take full, differential, or log backups with a single click, no T-SQL required. Free to download.

Download Free SQL Management Tool