Recovery Models in SQL Server

Recovery Model in SQL Server provides a way to manage database log files which helps in disaster recovery. SQL Server has three different types of recovery models and each model represents a different approach to balancing the tradeoff between conserving disk space and providing for granular disaster recovery options.

Different Types of Recovery Models are:

1)   Simple Recovery Model

2)   Full Recovery Model

3)   Bulk-logged Recovery Model 

Let’s see each model in detail now.

Simple Recovery Model

1.   In simple recovery model, SQL Server stores only minimum amount of information in the transaction log files.

2.   Transaction log files would be truncated on each time a transaction checkpoint occurs. Finally it will not have any log entries for disaster recovery plan.

3.   We may restore only full or differential backups and it is not possible for point-in-time recovery in simple recovery model database.

Full Recovery Model

1.   In Full recovery model, SQL Server stores all information in transaction log files.

2.   Transaction log files will not be truncated until we back it up. It allows us to design disaster recovery design which includes the combination of full, differential and transaction log backups.

3.   We can restore full, differential and transaction log files as part of disaster recovery plan. Point-in-time recovery is possible in full recovery model.

Bulk-logged Recovery Model

1.   It is a special purpose model that works similar to the full recovery model. The difference is that the way of handling bulk data modifications in which SQL Server uses minimal logging that saves on processing time significantly.

2.   Transaction log files will not be truncated until we back it up.

3.   We can restore full, differential and transaction log files as part of disaster recovery plan. But Point-in-time recovery is not possible in bulk-logged recovery model as it maintains only minimal logging during bulk data modifications.

4.   Microsoft recommends that the bulk-logged recovery model only be used for short periods of time. Best practice dictates that we switch a database to the bulk-logged recovery model immediately before conducting bulk operations and restore it to the full recovery model when those operations complete.