SQL Server Simple Recovery Model: When to Use It and When Not To

How much data a SQL Server database loses in a disaster is often decided by a single setting: the recovery model. It controls how the transaction log is managed, whether log backups are possible, and which restore scenarios are available. The simple recovery model is the easiest of the three to manage, and also the one that protects you the least.

This article explains exactly what the SQL Server simple recovery model does, how it differs from the full recovery model, when it is the right choice and when it is a risk, how to check and change the recovery model, and how to avoid breaking the log chain when you switch.

What Is the Simple Recovery Model?

Every SQL Server database has a recovery model, and there are three options: simple, full and bulk-logged. Under the simple recovery model:

• Transaction log backups are not supported. You can only take full and differential backups.
• Log space is reclaimed automatically. After each checkpoint, the part of the log that is no longer needed becomes reusable (log truncation). You don’t need log backups to keep the log under control.
• You can only restore to the end of a backup. Every change made after the last full or differential backup is lost in a disaster and has to be redone.

One detail worth knowing: according to Microsoft, SQL Server Enterprise and Standard editions create new databases with the full recovery model by default, while Express edition uses simple. New databases inherit the setting from the model database. An application that grew from Express to Standard may therefore still have its databases in simple mode without anyone noticing.

Simple vs Full vs Bulk-Logged Recovery Model

The bulk-logged model is used as a temporary alternative to full recovery during large bulk imports or index rebuilds, to limit log growth. It is rarely the right permanent choice.

When the Simple Recovery Model Is the Right Choice

Simple recovery isn’t a bad setting; it just has to be used in the right place. It is a reasonable choice for:

• Data that can easily be rebuilt: staging databases reloaded from source every night, ETL work tables, reporting copies.
• Development and test environments: copies of production where losing data has no business impact.
• Read-only or rarely changing databases: if changes only happen during a scheduled load, a full backup taken right after the load gives enough protection.
• Systems whose accepted data loss window is longer than the backup interval: if the business says “we can accept losing the last 24 hours” and a full backup runs daily, simple recovery meets that target.

When the Simple Recovery Model Is a Risk

If any of the following applies, the database belongs in the full recovery model:

• Transactional data you can’t afford to lose: orders, payments, accounting, ERP and CRM systems that are written to all day. Simple recovery with a nightly full backup means a disk failure in the afternoon wipes out every transaction from that day.
• Protection against user error: getting back to one minute before an unfiltered DELETE or UPDATE requires a point-in-time restore. That isn’t possible in simple mode.
• High availability and disaster recovery: Always On availability groups, database mirroring and log shipping don’t work with the simple recovery model.
• Contractual RPO targets: if you promise a customer “at most 15 minutes of data loss”, only the full recovery model with log backups every 15 minutes can deliver it.

How to Check the Recovery Model

One query shows the recovery model of every database and why its log can’t be truncated:

SELECT name,
       recovery_model_desc,
       log_reuse_wait_desc
FROM sys.databases
ORDER BY recovery_model_desc, name;

In SSMS, right-click the database, open Properties → Options and look at the Recovery model field.

Databases in full recovery with no log backups

The opposite problem is just as common: the database is in full recovery, but nobody takes log backups. The log then grows until it fills the disk. This query shows the last log backup date:

SELECT d.name,
       d.recovery_model_desc,
       MAX(b.backup_finish_date) AS last_log_backup
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b
       ON b.database_name = d.name
      AND b.type = 'L'
WHERE d.recovery_model_desc IN (N'FULL', N'BULK_LOGGED')
  AND d.database_id > 4
GROUP BY d.name, d.recovery_model_desc
ORDER BY last_log_backup;

If last_log_backup is empty or very old, either a log backup job needs to be set up, or you should ask whether the database really needs to be in full recovery. For more on transaction log growth, see Is Your SQL Server Database Transaction Log Too Big?

How to Change the Recovery Model

Any user with ALTER permission on the database can change the recovery model at any time:

USE [master];

-- Switch to simple
ALTER DATABASE [StagingDB] SET RECOVERY SIMPLE;

-- Switch to full
ALTER DATABASE [SalesDB] SET RECOVERY FULL;

The command takes effect immediately, but the switch affects your backup chain, and Microsoft’s guidance here is clear. The change also clears the database’s plan cache, so it’s better not to do it during peak hours.

Switching from full to simple

1. Take a log backup before switching, so that every transaction up to that point is protected.
2. The switch breaks the log chain. From that moment on, point-in-time restore is no longer possible.
3. Disable scheduled log backup jobs, otherwise they will start failing.
4. Make sure regular full (and, if needed, differential) backups are scheduled. They are now your only protection.

Switching from simple to full

1. Take a full or differential backup immediately after switching. According to Microsoft, the full recovery model only takes effect after the first data backup. Until then, the log keeps being truncated at checkpoint as if the database were still in simple mode.
2. Schedule regular log backups and update your restore plan. Without log backups, the log grows until the disk is full.

-- Safe switch from simple to full
ALTER DATABASE [SalesDB] SET RECOVERY FULL;

-- Take a full backup right away to start the log chain
BACKUP DATABASE [SalesDB]
TO DISK = N'E:\Backup\SalesDB_FULL_after_switch.bak'
WITH COMPRESSION, CHECKSUM, STATS = 10;

-- Then take regular log backups (e.g. every 15 minutes from an Agent job)
BACKUP LOG [SalesDB]
TO DISK = N'E:\Backup\SalesDB_LOG.trn'
WITH COMPRESSION, CHECKSUM;

Why Does the Transaction Log Still Grow in Simple Mode?

The promise that you “don’t need to manage the log” under simple recovery doesn’t always hold. The log is truncated at checkpoint, but log records that are still needed can’t be removed. The most common reasons:

• Long-running transactions: a single open transaction keeps all the log generated after it began. Microsoft notes that this prevents log truncation under every recovery model, including simple. A large overnight DELETE or index maintenance job are typical examples.
• A data backup or restore in progress: log truncation is delayed while a backup runs.
• Large operations in a single transaction: updating millions of rows in one statement makes the log grow until that statement finishes. Splitting such work into smaller batches is the most effective way to keep the log under control in simple mode.

The log_reuse_wait_desc column in sys.databases tells you why the log can’t be truncated. If you see ACTIVE_TRANSACTION, this query finds the open transaction:

SELECT s.session_id,
       s.login_name,
       s.host_name,
       dt.database_transaction_begin_time,
       DATEDIFF(MINUTE, dt.database_transaction_begin_time, SYSDATETIME()) AS open_minutes,
       dt.database_transaction_log_bytes_used / 1048576 AS log_used_mb
FROM sys.dm_tran_database_transactions AS dt
JOIN sys.dm_tran_session_transactions AS st ON dt.transaction_id = st.transaction_id
JOIN sys.dm_exec_sessions AS s ON st.session_id = s.session_id
WHERE dt.database_id = DB_ID(N'StagingDB')   -- replace with your database
ORDER BY dt.database_transaction_begin_time;

Simple recovery also has a real advantage: some operations, such as BULK INSERT, SELECT INTO and index creation or rebuilds, are minimally logged under the simple and bulk-logged models, which reduces log growth and run time. Under full recovery they are always fully logged.

Common Misconceptions About the Simple Recovery Model

• “Nothing is logged in simple mode.” Wrong. Every operation is still logged; that is required for crash recovery and rollback. The difference is when the log space can be reused.
• “Simple recovery improves performance.” Generally not. Normal OLTP operations are logged the same way in both models. The difference only shows up for bulk operations that can be minimally logged.
• “The log disk is full, let’s switch to simple and shrink.” In an emergency this seems to solve the problem, but it breaks the log chain and silently removes your point-in-time restore capability. The right fix is usually to take a log backup and correct the log backup frequency.
• “Differential backups don’t work in simple mode.” Wrong. Full and differential backups are both available in simple mode; only log backups aren’t.

Frequently Asked Questions