SQL Server Recovery Models

Understanding SQL Server Recovery Models is essential to develop an effective backup strategy.

A database recovery model determines how transactions are logged and type of backups that can be performed on a database.

There are three types of SQL Server Recovery Models:

1. Simple Recovery Model
2. Bulk-Logged Recovery Model
3. Full Recovery Model

Simple Recovery Model:

Simple recovery model supports full and differentials backups.
Truncate log on checkpoint option will be enabled in Simple recovery. Whenever checkpoint runs in this model committed data moves to mdf and inactive portion of virtual log file will be truncated, hence there will be no data in ldf file for backups.

Point-in-time restoration and point of failure when database crash is not possible. This model is used for development and test servers where data loss is acceptable.

Full Recovery Model:

Full Recovery Model provides highest level of data protection by fully logging all transactions. It supports all type of backups. We can recover and restore data fully. it is capable of point-in-time recovery.

Bulk-Logged Recovery Model:

Whenever we perform bulk operations log file may full due to huge transactions. No new transactions will comes to mdf. Hence, the transactions may fail.
In Bulk-Logged Recovery Model, it records minimal information about transactions. It logs the extent allocations and modified extents information only.
We cannot perform point-in-time recovery, whenever transactions including bcp, bulk insert, create index, alter index rebuild, dbcc dbreindex
Whenever we change recovery model always take a full backup to avoid data loss.

Powered by k2schools