Database recovery models

This post is about database recovery models for SQL Server databases. Having the correct recovery model for a database is crucial in terms of your backup and restore strategy for the database. It also defines if you need to do maintenance of the transaction log or if you can leave this task to SQL Server. Let’s look at the various recovery models and how they work.

The three database recovery models for SQL Server:

Full
Bulk-Logged
Simple

Full Recovery

Every operation is logged and written to the transaction log. This is required for very low or no data loss requirements and means that regular log backups are required. If they are not taken, the transaction log will grow indefinitely (or until it fills up the disk or hits the maximum sized specified). If this occurs, a 9002 error is generated, and no further data modifications can take place until the log is backed up or truncated. Full recovery model is usually used in production, especially for OLTP workloads.

Without a full backup being taken, databases in the full recovery model are treated as if they are in simple recovery model, the log is automatically truncated. So it is imperative to take a full backup of your database when you first use full recovery. (A differential backup would also suffice if there has previously been a full backup that would form part of your backup chain).

Choose the full recovery model if you want or need to do any of the following things:

Have point in time recovery
Minimise data loss including potentially no data loss
Recover to a marked transaction
If you want to restore individual pages
VLDBS with multiple filegroups where you might want to be able to utilise piecemeal restores
If you want to use availability groups, database mirroring or log shipping technologies

Bulk-Logged Recovery

Only extents that are modified are recorded in the transaction log. This means they are reliant upon the database’s data files. Certain types of transaction are classed as minimally logged including:

BCP
BULK INSERT
INSERT INTO SELECT
SELECT INTO
CREATE INDEX
ALTER INDEX
DROP INDEX

You are also required to take log backups with the bulk-logged recovery model. The log backups refer to the Bulk Changed Map (BCM) to identify modified extents in need of backup. Read up more on BCM: Further Reading

Choose the bulk-logged recovery model if you want or need to do any of the following things:

Improve performance of bulk-logged operations
Minimise the size of the transaction log

Simple Recovery

The transaction log is automatically managed by SQL Server and cannot be backed up. SQL Server reclaims the space used by automatically truncating the log. This recovery model is mostly used for non-prod environments. Although SQL Server manages the transaction log in simple recovery model, there is still a chance your t-log could fill up. This would occur if open transactions are left in place (not committed or rolled back) or by long running transactions.

Choose the simple recovery model in the following scenarios:

You can afford data loss since the latest full backup (or latest differential)
It’s a non-prod database and can easily be restored or refreshed
Read only databases
Rarely updated databases including data warehouses where the data is updated only once per day
If you are utilising a 3^rd part solution such as a SAN snapshots
- Important Note: If you are a DBA or have responsibility for backups/restores, make sure that you fully understand any backup technology in place and that you know how to recover using it.

Switching recovery model

The default recovery model is determined by the recovery model of the model database but can be changed at any time using an ALTER DATABASE command. If you do change the recovery model, you’ll need to understand the impact on the backup chain.

Switching from FULL or BULK_LOGGED to SIMPLE will break the log backup chain. It’s worthwhile taking once last log backup before making this switch, this will allow you point in time recovery up to that point.
Switching FROM SIMPLE to FULL or BULK_LOGGED requires a new full or differential backup (if full exists) in order to initialise a new log chain. So, take one of these backups as soon as possible so that you can start backing up your logs.

Verifying Recovery Model

There’s a handy DBATools PowerShell command for checking that the database you think are in Full Recovery model are actually in Full Recovery Model. Test-DbaDbRecoveryModel

Test-DbaDbRecoveryModel -SqlInstance localhost | Select InstanceName, Database, ConfiguredRecoveryModel, ActualRecoveryModel | Out-GridView

Note: In the output below, you can see that NebulaDataSolutions database is configured for FULL recovery but is actually in SIMPLE. This is because there is no valid Full backup for NebulaDataSolutions.