SQL Server Recovery Models and it’s Role in Backup Types

Stephen West ~ Modified: January 8th, 2019 ~ SQL, SQL Server 2016 ~ 4 Minutes Reading

Nowadays many users got confused on how Backup types are related to Recovery Model.Here I will let you know, what role does backup type play in SQL Server Recovery Model. But first, let us discuss What is Recovery Model and How to check it in SQL Server?

A recovery model is a database property that controls how transactions are logged, whether the log requires the need of backing up and what kind of restore operations are available.The recovery model tells SQL Server what kind of data to keep in the transaction log file and for how long.

There are three types of recovery models which are as follows:

Full
Simple
Bulk-Logged

What is FULL RECOVERY MODEL

The full recovery model allows you to recover all your data like in complete recovery model to any point in time as long as all backup files are useable.The full recovery model allows you to restore a database to a specific point in time. The advantage of this is no work is lost due to a lost or damaged data file.

What is SIMPLE RECOVERY MODEL

The simple recovery model maintains the minimal amount of information in which no log backup takes place.It gives you a simple backup that replaces your entire database in the event of a failure due to corruption or any accidental changes. If you need to restore your database to another server, the simple recovery model can be helpful. The advantage of using simple recovery is it permits high-performance bulk copy operations.

What is BULK-LOGGED RECOVERY MODEL

The bulk-logged recovery model works same as full recovery model, the only difference is that it handles bulk data modification operations.This model depends on the transaction log and provides complete recoverability through it. It prevents work loss in the broadest range of failure scenarios. The Bulk-Logged model provides high performance and lowers the log space consumption for certain large-scale operations.

How to check Recovery model in SQL Server?

The simplest way to check the type of Recovery model in SQL Server can be given by running following command:

SELECT name AS [Database Name], recovery_model_desc AS [Recovery Model] FROM sys.databases GO

Role Of SQL Server Recovery Model in Backup Types

Every database consists of having only one recovery model. Each database can have different recovery model.Depending on its processing and the backup needs, you can select accordingly, the appropriate recovery model per database. The role of recovery model in backup types is defined as follows:

In Simple Recovery Model, it can recover to a specific point in time, assuming that your backups are complete up to that point in time, we may restore full or differential backups only. It is not possible to restore such database to a given point in time, we may only restore it to the exact time when a full or differential backup occurred. Therefore, you will automatically lose any data modifications made between the time of the most recent full/differential backup and the time of the failure.

In Full Recovery Model,we have the most flexibility in restoring the databases using the full recovery model when a database failure happens. This allows us to design a disaster recovery plan that includes a combination of full and differential database backups in conjunction with transaction log backups.It can recover to a specific point in time, assuming that your backups are complete up to that point of time.

In Bulk-Logged Recovery Model, it can recover to the end of any backup, the transaction logs will not get that large if you are doing bulk operations. It still allows you to do point in time recovery as long as your last transaction log backup does not include a bulk operation.If no bulk operations are running, this recovery model works same as the Full recovery model. There is need to take transactional Log Backup otherwise your database transaction log will continue to grow.

Query to Find and Change the Type of Recovery Model in SQL Server Databases:

Query to View the Type of Recovery Model of All Databases:

SELECT name, recovery_model_desc FROM sys.databases

Query to View the Type of Recovery Model of specific Database:

SELECT name, recovery_model_desc FROM sys.databases WHERE name = ‘model’;

Query to change the type of recovery model:

USE master ;

ALTER DATABASE model SET RECOVERY FULL ;

Conclusion

This article explains you about the Recovery models and their role in various backup types. Depending on your need, how one can restore his/her database by using various Recovery Models. You will also get to know the query of finding the type of recovery model in SQL Server Databases.