What is a SQL Server Recovery Model?

When we meet with clients for an initial SQL Server Health Check, we’re sometimes asked what a SQL Server recovery model is. Once explained, the natural follow up question often is: well, then which recovery model should we use?

In this post, we will address both of these important questions.

What is a SQL Server Recovery Model?

In SQL Server environments, the recovery model setting is a database-level configuration that defines how transaction logs and backups are managed for each database. This setting determines how the transaction log is managed and whether point-in-time recovery options are available for restoring that database.

There are three recovery model settings in SQL Server: Simple, Full, and Bulk-Logged. Let’s look at each.

Simple Recovery Model

With the Simple Recovery Model, SQL Server essentially relieves you from having to manage the transaction log. It takes care of it for you. SQL Server automatically truncates the transaction at regular intervals. Scheduled backups of the transaction log are not taken.

As such when a database is set to the Simple Recovery Model, restoring transactions since the last full or differential backup is not an option. This means that data between backups has the potential to be lost in a crisis.

The Simple Recovery Model is designed for situations where point-in-time recovery is not of crucial importance.

When should you use the Simple Recovery Model?

Simple recovery should be used when the risk of data loss between scheduled full or differential backups is acceptable, and straightforward backup and recovery processes are preferred. Development, test, and non-critical production databases are often set to simple recovery.

            Pros

• Simplified backup process
• Straightforward recovery procedures
• Typically requires less storage for transaction logs

            Cons

• Recovery is limited to the time of the last full or differential backup
• Potential data loss of any transaction since the last data backup
• Cannot be used with log shipping, Always On Availability Groups, or Database Mirroring

Full Recovery Model

With the Full Recovery Model, transaction logs are retained until they are explicitly backed up. They are not automatically managed by SQL Server. The logs are only truncated after a log backup has been taken. Note: this means that regular transaction log backups are not just an option but a requirement in this model to prevent the log from growing too large. If the transaction log is not explicitly backed up, it may continue to grow unbounded and fill all available disk space. (You can set a maximum size for the transaction log to prevent it from filling the disk, however once that maximum size is reached the database will not be available for transactional purposes.)

The Full Recovery Model enables point-in-time recovery of your critical databases.

When should you use the Full Recovery Model?

The Full Recovery Model is most appropriate for critical databases where potential data loss between backups must be minimized.

It is also appropriate to use this model when using log shipping, Always On Availability Groups, or Database Mirroring. Likewise, it can be used for databases with frequent transactions since the frequency of log backups can be scheduled manually as needed. Practice Management Systems in healthcare, Enterprise Resource Planning Systems in manufacturing, and financial systems in FinTech applications typically use the Full Recovery Model.

            Pros

• Fully logged to support point-in-time recovery
• Offers the highest level of protection against data loss for mission-critical data

            Cons

• Requires more complex backup and recovery processes, including transaction log backups
• The transaction log can grow rapidly if not managed properly

Bulk-Logged Recovery Model

The Bulk-Logged Recovery Model is a hybrid approach to database recovery. It is designed for databases that see frequent bulk operations, like bulk inserts or index rebuilds that would produce large transaction logs, and whose RPO (recovery point objective) is shorter than the time interval between full or differential backups.

Like the Full Recovery Model, the Bulk-Logged Recovery Model requires explicit log backups. However, unlike the Full Recovery Model, Bulk-Logged recovery uses minimal logging while bulk operations are being performed, reducing the rate at which the transaction log grows and improving performance for these operations.

Full transaction logging of the bulk-operation is still retained in the log backup and can be leveraged for point-in-time recovery. However, transactions occurring during minimally logged bulk operations are vulnerable to loss until the operation is complete and the next log backup has been taken.

When should you use the Bulk-Logged Recovery Model?

The Bulk-Logged Recovery Model is most appropriate for databases (like in data warehouses or in data marts) where large-scale data operations are frequent.

            Pros

• Fully logged to support point-in-time recovery like the Full Recovery Model
• Offers performance improvement and reduced risk of log growth during bulk operations

            Cons

• Requires more complex backup and recovery processes, including transaction log backups
• There is limited point-in-time recoverability during bulk operations, and restoring to the most recent log backup would be necessary in the event of a failure

More information

Choosing the appropriate SQL Server Recovery Model for each of your database requires an understanding of data it contains, as well as the business’ RPO for the data. For example, if the business can afford to lose up to 24 hours of data, the nightly backups and a Simple Recovery Model may be a good approach. On the other hand, if minimal data loss is acceptable for a database, the Full Recovery Model with combination full backups, differential backups, and frequent log backups will likely be the model of choice.

Do you have questions about SQL Server Recovery Models or your recovery strategy? Are you unsure of what recovery models your SQL databases are using?

Here are some additional posts that may help:

• How to Align Your SQL Server to Your RPO and RTO Goals
• Where to Start with Disaster Recovery in SQL Server
• Using VM Snapshots to Backup SQL Server?
• Why performing database restores before a crisis strikes is a good idea

Or reach out to us! We would be happy to hop on a call or to set up an initial health check to help you perform an assessment. Schedule a call with us to get started.