Recovery model plays very important role when it comes to data recovery or to minimise data loss. You need excellent understanding of your business requirements and importance of data. Depending on scenarios you have to decide whether you have to select simple, full or bulk-logged recovery model. Recovery model plays important role to decide recovery strategy. For example, in banking operations you can’t take risk to lose any transaction and each record is very important.
In SQL Server, there are three types of recovery models.
1. Simple Recovery Model
2. Full Recovery Model
3. Bulk-logged Recovery Model
2. Full Recovery Model
3. Bulk-logged Recovery Model
How to check which recovery model is set for your database.
SELECT name "Database Name", recovery_model_desc FROM sys.databases GO
1. Simple Recovery Model
In simple recovery model, you will not be able to do log backups as it automatically truncates log. So it minimizes admin works and saves log space. But there are some risks also, if database is damaged or data has lost then you won’t be able to recover it if you don’t have backups. You can recover only the point where you have already taken backup. Simple recovery model truncates log after checkpoint, when checkpoint occurs SQL Server truncates log before the VLF which contains the Min. LSN value.
You will not be able to perform following operations in simple recovery model.
– Log Shipping
– Database Mirroring
– AlwaysOn feature
– Point-in-Time restores or data recovery etc.
You can use simple recovery model when:
– Data is not so important and you do not need point in time recovery.
– You can take risk to lose some data because your business requirement is different.
– You can manage if database is corrupted or damaged and you are comfortable losing data during the point between last backup and current failure point.
– You don’t want to backup and restore transaction log and you can manage with full backup and differential backup.
How to set Simple Recovery Model
USE master GO ALTER DATABASE Inventory SET RECOVERY SIMPLE GO
2. Full Recovery Model
Full recovery model is by default set when you install SQL Server. If you will create new databasethen recovery model will be set according to model database. In full recovery model alltransaction logs are retained till the time of backup. In this recovery model you can recover thedatabase till the point of failure if tail of the log is backed up.
You can use full recovery model when:
– You need point-in-time data recovery.
– Data is very important and you cannot lose any data.
– You want to use advanced features like log shipping, mirroring, AlwaysOn etc.
How to set Full Recovery Model
USE master GO ALTER DATABASE Inventory SET RECOVERY FULL GO
3. Bulk-logged Recovery Model
You can use bulk-logged recovery model when you are running very large operations or bulk operations. This recovery model reduces the transaction logging requirements for bulk operations. You can switch temporarily a database to bulk-logged recovery model when you are running bulk import or you are performing indexing operations on very large tables. It will increase performance using this recovery model rather than using full recovery model where each transactions are logged. This recovery model also retains transaction log records until they are backed up. Point-in-time recovery is not supported in bulk-logged.
Today I was going through recovery models so thought to share different ways how we can find recovery models in SQL Server 2012.
Option 1:
Execute below query in query editor and find the recovery model.
SELECT name, recovery_model_desc FROM sys.databases GO
Option 2:
Execute below query and check the recovery model in STATUS column.
EXEC sp_helpdb GO
Option 3:
You can find the Recovery model through SSMS also as given in below path.
Go to Object Explorer –> Databases –> Right click on Database –> Select Properties –> SelectOptions Page
Option 4:
In Object Explorer, Select Databases node and Press F7, you can find the recovery model underRecovery Model column.
Option 5:
Put your database name in query and execute.
SELECT DATABASEPROPERTYEX ('DBName', 'RECOVERY') "Recovery Model" GO
Option 6:
Right click on database and select Facets and you will find the detail in recovery model property.
If you know any other option, please do share with us. 
