Recovery Modals in SQL Server

Being a SQL Server DBA, one should be able to understand how many recovery modals do we have in SQL Server.While determining the Backup starategies for you databases , we should give a deep dive into the recovery modals of the databases as they define the types of backups that we can perform with the databases.

There are three types of Recovery Models in SQL Server:-

1) Full Recovery Model
2) Bulk-logged Recovery Model
3) Simple Recovery Model

Full Recovery Model:-

In this Model, we can take FULL, Differential & Log backups . Every transaction in is logged in the databases log file.

Bulk-logged Recovery Model

In this Model, we can take FULL, Differential & Log backups. The main difference between Full & Bulk logged is that all the Bulk Load operaions are minimally logged into the log file of the databases. In other words , we can say that only the allocation & deallocation of pages that aee affected with the Bulk operations are logged into the log file.

Simple Recovery Model

Under this recovery model, we are only capable of taking Full & Differential backups of the databases. Log backup is not possible in this recovery model.


How to change the Recovery Models in SQL Server

1) Go to the database properties
2) Go to the option tab

3) Select the recovery modal from the drop down box