Recovery Model in Microsoft SQL Server

In SQL Server, the recovery model is a very important concept or we can say, it is a configuration setting in which the system defines how the transaction log is used to recover the database at the time of any issue in the database.

SQL server has three recovery models for databases: 

    i) Simple, 

    ii) Full and 

    iii) Bulk-logged. 

Every recovery model has advantages and disadvantages.

To decide on the recovery model of any new database, we need to consider specific requirements and needs for the application.

Simple Recovery Model:-

Simple recovery is the basic and straightforward recovery model in SQL servers. 

This recovery model provides the least protection for our data.

In this recovery model, automatically log space reclaims to keep the space requirement small. 

This means system automatically truncates the transaction log when a checkpoint occurs.

In other words, the transaction log contains only uncommitted transactions.

If the transaction is committed, the system immediately writes into the database and deletes from the transaction log & reclaims the space.

If the database is in simple recovery mode, we can't take a T-Log backup.

With simple recovery mode, Log Shipping, Mirroring, and Always On high availability features can't be configured.  

The Recovery Model ID of full is 3.

Bulk-Logged Recovery Model:-

The Bulk-Logged recovery model contains few features of Simple and Full recovery models & is designed for Bulk Operations. 

This model truncates transaction logs like the Simple Recovery Model.

To enhance performance, certain bulk operations are minimally logged in the system.

It captures all other operations.

Hence, point-in-time recovery is not possible in this recovery model.

Bulk operations are the operations that work on large volumes of data. For example:- bulk inserts, select * into operations, index rebuilds, etc.

We generally use the Bulk-Logged recovery model in large-scale databases like data warehouses, batch processing applications, or reporting databases that don't require point-in-time recovery and can manage the loss of some data.

The Recovery Model ID of full is 2.

Full Recovery Model:-

In full recovery mode, all operations are fully captured in the transaction log.

In this model, it provides the highest level of protection for our data.

Also, a transaction log is not truncated after the checkpoint.

Hence, point-in-time recovery is possible in this recovery model.

We can take all types of database backups like full, differential, t-log, etc.

The model is suitable for applications that cannot afford to data lose.

The Recovery Model ID of full is 1.

FAQs:-

Q. How to check the recovery mode of a database?

A. We can use below ways to check the recovery mode of a database:-

    i). Select Database -> Select Properties -> Select Options -> Check Recovery Mode of the database.

    ii). Use the below query to get Recovery details:-

    SELECT Name as DatabaseNae,recovery_model AS

    RecoveryModelID, recovery_model_desc AS 

    RecoveryModelName FROM sys.databases

Q. What are SQL Server recovery models? Explain.

A. All Recovery Models in the SQL server are explained above. 

Q. What is a recovery model in SQL Server?

A. In SQL server, there are three recovery models, Bulk-Logged and Simple.

Q. Difference between the MSSQL recovery model simple vs full.

A. In Full, Point-in-Time Recovery is possible but in Simple, it is not possible.

In Full, all types of DB backup are possible but in Simple, T-Log Backup is not possible.

In Full, we can use the database in high availability but in Simple, a very limited HA option is available.

Q. Difference between the MSSQL recovery model full vs simple.

A. Just refer above question for the answer.

Q. What is the SQL Server recovery model simple?

A. Please refer Simple Recovery Model section of this article.

Q. What is the SQL Server recovery model full

A. Please refer Full Recovery Model section of this article.

Q. How to change the recovery model in SQL Server?

A. We can change the recovery model of a database using the below options:-

    i). Select Database -> Select Properties -> Select Options -> Check Recovery     Mode of the database & change the recovery mode -> Finally press OK to apply.

    ii). To change the Recovery model of the database, use the below command:-

    USE [master] ;  

    ALTER DATABASE [DatabaseName] SET RECOVERY FULL;

Q. Query to get SQL Server recovery model in SQL Server.

A. Use the below query to get 

    SELECT name AS DatabaseName, recovery_model_desc 

    AS RecoveryModelName FROM sys.databases 

    WHERE name = 'DatabaseName'

Q. What are the differences between the full and bulk-logged recovery models?

A. The full recovery model, captures each & every transaction in the log but in bulk-logged, certain bulk operations are minimally logged.

    Point-in-time recovery is possible in the Full Recovery Model but in Bulk-Logged, it is not possible.