A recovery model is a database configuration option that determines the type of backup that one could perform and provides the ability to restore the data or recover it from a failure.

The recovery model decides how the transaction log of a database should be maintained and protects the data changes in a specific sequence, which may later be used for a database restore operation.

Types of recovery models

All SQL Server database backup, restore, and recovery operations are based on one of three available recovery models:

• SIMPLE
• FULL
• BULK_Logged

SIMPLE

The SIMPLE recovery model is the simplest among the available models. It supports full, differential, and file level backups. Transaction log backups are not supported. The log space is reused whenever the SQL Server background process checkpoint operation occurs. The inactive portion of the log file is removed and is made available for reuse.

Point-in-time and page restore are not supported, only the restoration of the secondary read-only file is supported.

Reasons to choose the simple database recovery model

1. Most suited for Development and Test databases
2. Simple reporting or application database, where data loss is acceptable
3. The point-of-failure recovery is exclusively for full and differential backups
4. No administrative overhead

It supports:

1. Full backup
2. Differential backup
3. Copy-Only backup
4. File backup
5. Partial backup

Continue reading Understanding database recovery models in SQL Server

Happy Learning!