Database Recovery Model In SQL Server

Recovery Models Are Used To Manage Database Log File For Disaster Recovery. There Are Three Types Of Recovery Model Offered By SQL Server

• Simple
• Full
• Bulk Logged

Every Recovery Model Has Advantage And Disadvantages Over Each Other. Let’s See What They Are? And How They Affect Log File ?.

Simple Recovery Model:

• Its Very Simple as name suggests.
• Log is not maintained in this recovery model only small amount of information is stored in log file.
• Log is truncated on every CheckPoint.
• Transaction Log Backup cannot be taken in this recovery model.
• Point in time recovery is not possible.
• Database Mirroring and Log Shipping is not Possible With This Recovery Model.

Full Recovery Model:

• It Is Default Recovery Model In SQL Server. If You Create any database default recovery model of the database will be Full.
• Until You Take Full Backup of database It acts As Special Recovery Model Called(Pseudo_Simple Recovery Model).
• Full , Differential And Transaction Log Backup Is Possible in this recovery Model and With these combination of Backup you can design your disaster recovery.
• Once You take full backup of database, SQL Server starts preserving transaction log of database.
• The log files grows until you take Transaction log backup of database. if you don’t take regular transaction log backup, your transaction log will keep on growing and will grow until it runs out of disk space. It this case your database may be marked as suspect.
• Point In Time Recovery Is Possible.

Bulk Logged Recovery Model :

• Bulk Logged Recovery Model is similar as Full Recovery Model except it has a special feature called minimal logging.
• Logging of Bulk Operations (Bulk Operation Can Be Re-Building Index, BCP Command) is different then full Recovery Model.
• For Example If You Insert 20000 Rows From CSV file into a database Table then Under Bulk Logged Recovery Model Not Every Rows Is Logged in Transaction Log Rather a Bulk Operation is Logged in Transaction Log.
• It is recommended that Bulk Logged Recovery Model should be used only when you need to perform bulk operation on database.
• Mirroring Is Not Supported With Bulk Logged Recovery Model.
• Point In Time Recovery Is Not Guaranteed.

Conclusion :

• When You Don’t Need Transaction Log Or Point in time recovery is not required Or in other words data loss is accepted then Use Simple Recovery Model
• When You Set This Option For A Database
• Exec Sp_dboption 'DatabaseName','trunc. log on chkpt.','True' 
• It it as good as using a Simple Recovery Model.
• When Your Database is in Full Recovery Model and You need to perform Some Bulk Operation on Database Then
• Switch Database Recovery Model From “FULL” To “BULK LOGGED”
• Perform Bulk Operation
• Switch Database Recovery Model From “BULK LOGGED” To “FULL”

 

How To See  Or Change Recovery Model Of Database ?

Run Following Query To See Recovery Model Of All Databases.

Select name As DatabaseName,recovery_model_desc 
from Sys.databases
Go

Change The Recovery Model Of A Database

Alter Database DatabaseName Set Recovery Full

Alter Database DatabaseName Set Recovery Bulk_Logged

Alter Database DatabaseName Set Recovery Simple

You Can Do This with SQL Server Management Studio  (SSMS)

Select Database From Object Explorer In SSMS Right Click and Choose Property