This simple blog will cover the bulk of information you’ll need to make a fully informed decision on which Recovery Model to choose for your SQL Server databases. I know, that was horrible, but I couldn’t resist. Recovery Models are normally considered one of the more basic things to know about SQL Server, but refreshers are never a bad idea. Furthermore, if you are like me, you haven’t actually dealt with the Bulk Logged model much.
Simple Recovery Model
The simple model constantly reclaims log space in order to keep storage requirements minimal. Transactions are still written to the log; however, once a transaction completes and the data is written to the data file, the transaction log is truncated. This keeps the log small and allows the space to be reused.
While this model takes the least amount of planning and work to maintain, it also provides the least amount of protection. You cannot perform transaction log backups, so any changes since your last backup will be lost in the event of a disaster. There is no possibility to recover to a specific point in time either, and high availability solutions cannot be implemented. This model is best used for unimportant or test data. Data that rarely changes or would have no impact if data loss occurs between backups are also good choices for Simple recovery.
- Log shipping
- Database Mirroring
- Always On Availability Groups
- Point in time Restore
Bulk Logged Recovery Model
This model performs minimal logging of bulk operations in order to reduce the size of the transaction log. This means that the model does support the use of transaction log backups. In fact, it’s essentially identical to the Full Recovery model except for omitting bulk operations from the log. Even so, you shouldn’t use this model long term, just go full.
Bulk operation examples:
- Bulk Imports (BCP, Bulk Insert)
- CREATE/ALTER INDEX
- SELECT INTO
- WRITETEXT & UPDATETEXT
Since Point in Time recovery is not supported in this model, data can be lost, but only if the transaction log is damaged or bulk logged operations occur (since those aren’t logged). This recovery model is best used during index maintenance or when inserting massive amounts of data. Your transaction log won’t blow up in size, and you’ll be able to flip back to Full Recovery easily after your maintenance is complete (more on that later).
- Transaction Log
Full Recovery Model
With the Full Recovery model, you can prevent data being lost due to damage and you finally have the opportunity to restore to a specific point in time. All transactions are stored in the log until a log backup occurs or the log is truncated. If you fail to schedule regular log backups, your transaction log will grow till storage becomes a major problem. After a transaction completes, the data from the log is then written into the data file.
If the tail of the log is damaged, you can still have some data loss up to the last transaction log backup. This is the most appropriate recovery model to use for production data as its the only way to provide point in time recovery.
- Transaction Log
Changing Recovery Models
The command to flip recovery models is very easy, just issue an
alter command on the desired database and
set it to the desired model.
USE [Master]; ALTER DATABASE TestDB SET RECOVERY FULL;
Flipping between recovery models requires a few considerations, which I’ve summed below.
|Full to Bulk||Backup log for a last recovery point then switch to Bulk Logged Recovery; backup log again after the switch.
Backup Log -> Switch to Bulk-> Backup Log
|Bulk to Full||Switch back to Full Recovery after Bulk Logged operations complete; backup the log.
Switch to Full -> Backup Log
|Simple to Full/Bulk||Switch to Full or Bulk Recovery; perform a Full or Differential Backup to start the log chain. Schedule or reenable log backups. Change takes effect after first backup.
Switch to Full/Bulk -> Data Backup -> Backup Log
|Bulk/Full to Simple||Disable Log Backups, breaking the log chain. Switch to Simple Recovery; perform a Full or Differential Backup.
Disable Log Backups -> Switch to Simple -> Data Backup