Recovery Model Behavior

Full Recovery Model

This recovery is the most common recovery model used for SQL Server. This model is required for database mirroring and availability groups.

• Allows log backups and zero-to-minimal data loss after a disaster
• All changes to the database are fully logged to the transaction log file
• The transaction log will not clear data until a log backup has been performed, meaning that the log backup is what clears the transaction log
• This means that log backups are required, otherwise the transaction log will continually grow until your drive is full

Simple Recovery Model

Commonly used when log backups and point-in-time recovery are not required.

• Some operations can use minimal logging
• All other operations are fully logged, as in the FULL recovery model
• The transaction log will not clear data until a checkpoint has been performed
• Log backups are NOT possible with this recovery model

Important: Disaster recovery is only possible using full and differential backups, so data loss will occur back to the most recent data backup. Simple recovery is not an appropriate choice for production systems where loss of recent changes is unacceptable.

Bulk-Logged Recovery Model

Commonly used to minimize log growth during bulk operations, while preserving the ability to take log backups.

• Some operations can use minimal logging
• All other operations are fully logged, as in the FULL recovery model
• The transaction log will not clear data until a log backup has been performed, meaning that the log backup is what clears the transaction log
• This means that log backups are required, otherwise the transaction log will continually grow until your drive is full
• The time running in this mode should be minimized to reduce potential loss of data

Important: The Bulk-Logged recovery model should not be used if there is the possibility of losing user transactions.

Microsoft has a high level overview of recovery model differences.