Recovery Models in SQL Server

Recovery Models in SQL Server :

Recovery models are designed to control transaction log maintenance .

We need to choose the recover model based on the importance of the data .

Types of Recovery Models :

                                         a . Full
                                         b . Bulk - Logged
                                         c . Simple

a . Full :
               In Full recovery model we can recover the data upto point of failure because transaction logs are maintained  so that we can take the transaction log backups .

               In order to configure Logshipping , Database Mirroring and Replication database recovery model should be full .

               Maintenance will be huge for this recovery model .

               Recommended for OLTP Systems ( Online transaction processing )


b.  Bulk - Logged : 
               
                 Bulk - Logged recover model is used to recover the data upto point of failure . But we will lost bulk - operations because those are not recorded in to the log .
 
                  We can set this option before performing bulk operations such as Bulk Insert , BCP ,Select Into and Create Index on huge table etc to speed up the bulk operations .

c . Simple :

                We can recover the data only up to the last full / diff backup .
               
                Any further modifications will be lost after full / diff backup because SQL Server doesn't maintain transaction log so transaction log backup cannot be taken .

                We cannot configure Log-shipping , Database Mirroring and Replication on database  due to transaction log backup cannot be taken .

                 Maintenance will be less .

                Recommended for Development , Test and Data Ware houses environments and not often changed database .

                In simple recover model , the transaction logs are truncated  automatically

Note:

      1 . Except Model database all system databases recovery model is simple .
      2 .  Model database recovery model is Full .

      3 . By default any user database recovery model should be Full and its recovery model will change based on its usage and environment such as Dev , Test , Pre - Prod , Prod  and Data warehouses.

How to find the database recovery model :

Select databasepropertyex('databasename','recovery') as [Recovery Model ]

                                                         (or )
select distinct name as DatabaseName , recovery_model as RecoveryModel, recovery_model_desc  as RecoverModelDesc from sys.databases

Ex:

Select databasepropertyex('AdventureWorks','recovery') as [Recovery Model ]

How to set recovery model :

Use Master

Alter Database <database name > set Recovery Simple / Bulk_Logged / Full .

Ex:

Use Master

Alter Database AdventureWorks set Recovery Full

Alter Database AdventureWorks set Recovery Bulk_Logged

Alter Database AdventureWorks set Recovery Simple


Points Remember :

To implement logshipping database recover model should be either Full / Bulk - Logged .
To implement Replication database recover model should be either Full / Bulk - Logged .
To implement Mirroring database recover model should be either Full / Bulk - Logged .