SQL Server | Recovery Models

When managing SQL Server databases, one configuration directly influences data recoverability, transaction log management, backup strategy, and disaster recovery capability: the Recovery Model.

SQL Server provides three recovery models:

• Simple
• Full
• Bulk-Logged

Choosing a recovery model is not simply about controlling transaction log growth. It determines how transaction log records are managed for recovery and what restore options are available when something goes wrong.

DBA Rule: Choose a recovery model based on the business recovery requirement, not simply on transaction log size.

What Is a Recovery Model in SQL Server?

A recovery model is a database-level property that controls how transactions are logged and, importantly, how the transaction log can be backed up and managed for recovery purposes.

The selected recovery model affects:

• Transaction log backups
• Transaction log truncation
• Point-in-time recovery
• Backup and restore strategy
• Recovery objectives following a failure
• Transaction log space management

SQL Server Recovery Models at a Glance

Recovery Model	Log Backup	Point-in-Time Recovery	Typical Usage
Simple	No	No conventional log-based PITR	Development, test, staging, rebuildable databases
Full	Yes	Yes	Production and business-critical databases
Bulk-Logged	Yes	Limited when minimally logged operations are involved	Special bulk-operation scenarios

Check the Current Recovery Model

Use the following query to check the recovery model configured for SQL Server databases:

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

1. Simple Recovery Model

Under the Simple Recovery Model, transaction log backups are not supported.

SQL Server can reclaim reusable portions of the transaction log after checkpoints, provided another condition is not preventing log truncation.

This reduces log-backup administration, but it also limits recovery capability.

Example

Assume the following situation:

• Latest database backup: 12:00 AM
• Database failure: 4:00 PM
• Recovery model: Simple

You cannot use transaction log backups to restore this database to 3:59 PM because transaction log backups are not available under Simple recovery.

Recovery is therefore dependent on the latest usable full or differential database backup.

Change a Database to Simple Recovery

ALTER DATABASE [YourDatabase]
SET RECOVERY SIMPLE;

When Is Simple Recovery Appropriate?

Simple recovery can be appropriate for:

• Development databases
• Test environments
• Staging databases
• Reporting databases that can easily be rebuilt
• Databases where losing changes since the last backup is acceptable

When Should Simple Recovery Be Avoided?

Avoid using Simple recovery when the business requires:

• Point-in-time recovery
• Transaction log backups
• A very low Recovery Point Objective (RPO)
• Recovery to a moment immediately before accidental data modification

2. Full Recovery Model

For most important production databases, the Full Recovery Model is normally selected when the business requires granular recovery capabilities.

Under Full recovery:

• Transactions are fully logged
• Transaction log backups are supported
• A transaction log backup chain can be maintained
• Point-in-time restoration can be performed when the required backup chain is available

Enable Full Recovery

ALTER DATABASE [YourDatabase]
SET RECOVERY FULL;

Important: Simply changing a database from Simple to Full is not the complete backup strategy. Take a full or differential database backup after the change before relying on subsequent transaction log backups for recovery.

Take a Full Database Backup

BACKUP DATABASE [YourDatabase]
TO DISK = 'D:\SQLBackup\YourDatabase_FULL.bak'
WITH COMPRESSION, STATS = 10;

Take a Transaction Log Backup

BACKUP LOG [YourDatabase]
TO DISK = 'D:\SQLBackup\YourDatabase_LOG.trn'
WITH COMPRESSION, STATS = 10;

Understanding the Transaction Log

To properly understand recovery models, it is important to understand the role of the SQL Server transaction log.

SQL Server records database modifications in the transaction log. The transaction log is fundamental to transactional consistency and database recovery.

Application Transaction
↓
Transaction Log
↓
Modified Data Pages
↓
Database Data Files

The transaction log allows SQL Server to:

• Roll back incomplete transactions
• Roll forward committed transactions during recovery
• Support database restore operations
• Maintain transactional consistency
• Support various high-availability and disaster-recovery technologies

Full Recovery Does Not Mean the Log Should Grow Forever

One of the most common misconceptions is:

"Full recovery model causes the transaction log to grow."

This statement is incomplete.

With Full recovery, SQL Server normally expects transaction log backups as part of the backup strategy.

If required transaction log backups are not being performed, reusable log space might not become available as expected, and the physical log file can continue growing.

FULL Recovery
↓
Transactions Generated
↓
Transaction Log Grows
↓
Regular Log Backup
↓
Inactive Log Records Become Reusable
↓
Log Space Can Be Reused

Remember: Transaction log truncation does not normally reduce the physical size of the .ldf file. Truncation makes eligible internal log space available for reuse.

Why Is My Transaction Log Not Truncating?

When a transaction log continuously grows, do not immediately shrink the log file. First determine what is preventing log-space reuse.

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

Common log_reuse_wait_desc Values

• NOTHING - No current truncation blocker is reported.
• LOG_BACKUP - A transaction log backup is required.
• ACTIVE_TRANSACTION - An active transaction is preventing log truncation.
• AVAILABILITY_REPLICA - An Availability Group replica may still require the log records.
• REPLICATION - Replication-related processing may be preventing truncation.
• DATABASE_MIRRORING - Database mirroring may be retaining log records.
• CHECKPOINT - A checkpoint-related condition is involved.

Check for an Active Transaction

DBCC OPENTRAN('YourDatabase');

This can help identify an old active transaction that may be preventing transaction log truncation.

Transaction Log Backup Strategy

Under Full recovery, log backup frequency should be aligned with the application's Recovery Point Objective (RPO).

For example:

12:00 AM → Full Backup
12:15 AM → Log Backup
12:30 AM → Log Backup
12:45 AM → Log Backup
01:00 AM → Log Backup
...and so on

If the business can tolerate approximately 15 minutes of data loss under normal backup-based recovery assumptions, a 15-minute transaction log backup schedule may be considered.

If the requirement is around 5 minutes, transaction log backups may need to run approximately every 5 minutes.

The schedule should be designed according to the business RPO rather than selected arbitrarily.

Point-in-Time Recovery

One of the most important advantages of Full recovery is the ability to restore a database to a particular point in time when the required backup chain is available.

Suppose someone accidentally runs:

DELETE FROM dbo.Customer;

Assume the command was executed at 10:35 AM.

You may want to recover the database to:

10:34:59 AM

A simplified restore sequence would be:

Full Backup
↓
Latest Differential Backup
↓
Transaction Log Backups
↓
STOPAT 10:34:59
↓
Database Recovery

Example Restore

RESTORE DATABASE [YourDatabase]
FROM DISK = 'D:\SQLBackup\YourDatabase_FULL.bak'
WITH NORECOVERY;

Then restore the required transaction log backups in sequence:

RESTORE LOG [YourDatabase]
FROM DISK = 'D:\SQLBackup\YourDatabase_LOG1.trn'
WITH NORECOVERY;

Finally, restore the log that contains the required recovery point:

RESTORE LOG [YourDatabase]
FROM DISK = 'D:\SQLBackup\YourDatabase_LOG_Last.trn'
WITH STOPAT = '2026-10-27T10:34:59',
     RECOVERY;

Tail-Log Backup

Consider a database failure at 4:00 PM, with the latest scheduled transaction log backup completed at 3:45 PM.

If the transaction log remains accessible, a DBA may be able to take a tail-log backup before beginning the restore process.

Last Log Backup: 3:45 PM
↓
Additional Transactions
↓
Database Failure: 4:00 PM
↓
Tail-Log Backup

Example:

BACKUP LOG [YourDatabase]
TO DISK = 'D:\SQLBackup\YourDatabase_TAIL.trn'
WITH NORECOVERY;

Whether a tail-log backup can be performed depends on the condition of the database and transaction log.

3. Bulk-Logged Recovery Model

The Bulk-Logged Recovery Model is closely related to Full recovery but permits minimal logging for qualifying bulk operations.

Depending on the operation and conditions, minimally logged operations can include certain:

• Bulk imports
• BULK INSERT operations
• SELECT INTO operations
• Index operations

Enable Bulk-Logged Recovery

ALTER DATABASE [YourDatabase]
SET RECOVERY BULK_LOGGED;

A controlled scenario might look like:

FULL
↓
BULK_LOGGED
↓
Large Qualifying Bulk Operation
↓
Transaction Log Backup
↓
FULL

Important: Do not switch to Bulk-Logged simply because a large data load is running. The DBA should understand its logging and restore implications before making the change.

Full vs Bulk-Logged: Important Recovery Consideration

When a transaction log backup contains minimally logged operations, point-in-time recovery options within that particular log backup are restricted.

Therefore, Bulk-Logged should be treated as a deliberate operational decision rather than a general performance-tuning option.

What Happens When You Switch Recovery Models?

Simple → Full

ALTER DATABASE [YourDatabase]
SET RECOVERY FULL;

Follow the change with a full or differential database backup before relying on transaction log backups for the new recovery strategy.

Full → Simple

ALTER DATABASE [YourDatabase]
SET RECOVERY SIMPLE;

Moving from Full to Simple changes the recovery strategy and interrupts the log backup chain.

Avoid this shortcut:

FULL → SIMPLE → FULL

Do not use recovery-model switching merely as a quick way to control transaction log growth.

Why DBCC SHRINKFILE Is Not a Log Management Strategy

Suppose a transaction log grows to 100 GB. A DBA may be tempted to immediately run:

DBCC SHRINKFILE(YourDatabase_log, 1024);

The first question should instead be:

Why did the transaction log grow to 100 GB?

Possible reasons include:

• Missing transaction log backups
• Long-running transactions
• Availability Group synchronization issues
• Large DELETE, UPDATE, INSERT or bulk operations
• Index maintenance
• Replication issues
• Insufficient initial log sizing
• Poor autogrowth configuration

Repeated shrinking can create a cycle such as:

100 GB
↓
Shrink to 1 GB
↓
Workload Continues
↓
Grow Back to 100 GB
↓
Shrink Again

A properly sized transaction log is generally preferable to repeatedly shrinking and regrowing the file.

Checking Transaction Log Space

One simple instance-wide command is:

DBCC SQLPERF(LOGSPACE);

This helps the DBA distinguish between the physical size of the log file and the percentage of log space currently in use.

Understanding the Transaction Log Backup Chain

A simplified recovery chain can be visualized as:

Full Backup
│
├── Log Backup 1
├── Log Backup 2
├── Log Backup 3
└── Log Backup 4

SQL Server requires the appropriate sequence of backups to restore the database to the desired recovery point.

Backups therefore need to be:

• Successfully created
• Monitored
• Retained according to policy
• Protected against loss
• Accessible during a disaster
• Periodically restore-tested

Production Rule: A backup strategy is only trustworthy when the restore process has also been tested.

Does a Full Backup Break the Log Chain?

No.

Taking another full database backup does not by itself break the transaction log backup chain.

Full Backup A
↓
Log 1
↓
Log 2
↓
Full Backup B
↓
Log 3
↓
Log 4

Differential Backups and Recovery Models

Differential backups can reduce restore time for large databases by reducing the number of backup files that need to be applied before subsequent transaction logs.

For example:

Sunday → Full Backup
Monday → Differential Backup
Tuesday → Differential Backup
Wednesday → Differential Backup

A Wednesday restore might therefore use:

Sunday Full Backup
↓
Latest Wednesday Differential
↓
Required Transaction Log Backups
↓
Recovery

Understanding RPO and RTO

Recovery Point Objective (RPO)

RPO answers the question:

How much data can the business afford to lose?

For example, an RPO of 5 minutes means the backup and recovery architecture should be designed around limiting potential data loss to approximately five minutes under the defined failure scenario.

Recovery Time Objective (RTO)

RTO answers:

How long can the business afford the database to remain unavailable?

Recovery model and backup frequency should therefore be designed together with both RPO and RTO.

Example Production Backup Strategy

There is no universal schedule, but an example business-critical database configuration might be:

Backup Type	Example Frequency
Full Backup	Weekly
Differential Backup	Daily
Transaction Log Backup	Every 5–15 minutes

The actual schedule should consider:

• Business RPO
• Business RTO
• Database size
• Transaction volume
• Backup storage capacity
• Restore performance
• HA/DR architecture
• Backup retention requirements
• Regulatory requirements

Recovery Model vs Backup Type

These two concepts should not be confused.

Recovery Models

• Simple
• Full
• Bulk-Logged

Backup Types

• Full
• Differential
• Transaction Log
• Copy-Only
• File/Filegroup
• Partial

Real-World Production Scenario

Consider a financial application with the following requirements:

• Maximum acceptable data loss: 5 minutes
• Point-in-time recovery required
• Continuous business transactions
• Recovery from accidental data deletion required

A suitable starting design could be:

Recovery Model: FULL

Full Backup: Weekly

Differential Backup: Daily

Transaction Log Backup: Every 5 minutes

The recovery process could then follow:

Full Backup
↓
Latest Differential
↓
Required Transaction Logs
↓
Point-in-Time Recovery

Common DBA Mistakes

1. Full Recovery Without Log Backups

Setting the database to Full recovery but failing to schedule transaction log backups can result in uncontrolled transaction log growth.

2. Regularly Shrinking the Transaction Log

Repeated shrinking and regrowth does not address the reason the transaction log became large.

3. Switching to Simple Just to Truncate the Log

This can disrupt the intended Full recovery backup strategy and should not be used as routine log maintenance.

4. Using Bulk-Logged Without Understanding Recovery

The potential reduction in logging must be considered alongside the recovery restrictions associated with minimally logged operations.

5. Assuming a Full Backup Provides Point-in-Time Recovery

A full backup by itself does not provide conventional point-in-time recovery. The required transaction log backup chain must also be available.

6. Ignoring Backup Job Failures

A perfectly designed backup schedule provides little protection if backup jobs are failing and nobody notices.

7. Never Testing Database Restores

Successful backup jobs do not automatically guarantee successful disaster recovery.

Remember: Backup success tells you that SQL Server created a backup. Restore testing demonstrates that your recovery process actually works.

Useful DBA Queries

Check Recovery Models

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

Check Log Reuse Wait

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

Check an Open Transaction

DBCC OPENTRAN('YourDatabase');

Check Transaction Log Usage

DBCC SQLPERF(LOGSPACE);

Which Recovery Model Should You Choose?

Need Point-in-Time Recovery?
│
├── NO → SIMPLE
│
└── YES → FULL
        │
        └── Special qualifying bulk workload?
           → Evaluate BULK_LOGGED

For most business-critical production databases, Full recovery should be evaluated first against the organization's RPO and RTO requirements.

For development, testing, staging or easily rebuildable databases where point-in-time recovery is unnecessary, Simple recovery may be completely appropriate.

Bulk-Logged should normally be treated as a specialized operational option rather than a default recovery model.

The DBA Mindset

The question should not be:

"Which recovery model will keep my transaction log small?"

The better question is:

"If this database fails right now, exactly how much data can I recover, and how long will recovery take?"

This connects the entire recovery strategy:

Business Requirements
↓
RPO + RTO
↓
Recovery Model
↓
Backup Strategy
↓
Backup Retention
↓
Restore Testing
↓
Disaster Recovery

Final Thoughts

Understanding SQL Server recovery models is fundamental for anyone responsible for production databases.

SIMPLE

Provides simpler transaction log management but does not support transaction log backups. It is suitable when point-in-time recovery is not required.

FULL

Designed for environments requiring transaction log backups, detailed restore options and point-in-time recovery.

BULK-LOGGED

A specialized recovery model that can provide minimal logging for qualifying bulk operations but introduces additional recovery considerations.

Choose the recovery model based on how the business needs to recover data, not based on how much transaction log space you want to save.

What's Next?

The natural next topic in our SQL Server Administration journey is:

SQL Server Backup & Restore Strategy | Full, Differential & Transaction Log Backups

We will explore backup chains, LSNs, COPY_ONLY backups, tail-log backups, STOPAT, restore sequences, RPO/RTO planning and practical disaster-recovery scenarios.

---

Technical Architects Zone (TAZ)
Database • Cloud • Architecture • Learning