Recovery Models

Hi Today You will know about Recovery models

What is a Recovery Model :

It is determined how the transactional log is managed by SQL Server.It wont affect how the log is written but it affects how long the log entries remain in the transactional log.

Types of Recovery Models :

There are three types of Recovery Models.SIMPLE,FULL,BULK_LOGGED
Simple Recovery Model :
In this recovery model, the transactions log entries are kept only for the purpose of database integrity and are not kept for database recovery purposes. Once the log entries are marked as inactive, that is once the associated data pages have been written to disk, the log entries can be discarded.  In simple recovery mode, when a checkpoint operation runs, all inactive log records are removed from the transaction log and the space is made available for reuse.
This is the simplest recovery model in terms of log management as the log manages itself. In simple recovery model the transaction log backups cannot be made, recovery of the database can only be done up until the latest full or differential database backup.
The Simple Recovery Model does not support some high availability features such as log shipping and mirroring.
Full Recovery model:
In full recovery model transaction log entries are kept for both database integrity and database recovery purposes. Inactive log records are hold in the transaction log until a log backup occurs.In full recovery, all operations are fully logged, including operations that qualify as bulk operations.
Full recovery can be difficult to manage as the log can grow beyond expected if transaction log backups don’t occur, or if there’s an increase in the amount of database activity that occurs between log backups.  Because log records are not discarded until they have been backed up, a database in full recovery mode can be recovered to any time using a combination of full, differential and log backups.
Bulk-logged recovery model:
Bulk-logged is very similar to full recovery, except that in bulk-logged, bulk operations are minimally logged.  When operations are minimally logged, the full details are not written to the transaction log, however all extent and page allocations are still logged.
The advantage of bulk-logged recovery is that if there are bulk operations occurring, the impact those operations have on the transaction log is less than it would be if the database was in full recovery mode. However the transaction log backups may be much larger than the transaction log itself since the log backups include any data pages modified by bulk operations since the previous log backup.

How to see Recovery Model for particular database :
There are so many methods to see recovery model of a particular database.
By using T-SQL and SSMS
T-SQL :
SELECT DATABASEPROPERTYEX ('DATABASENAME','RECOVERY')
SSMS :
RIght click on Particular database and select properties then properties window will open, In properties window select options tab there you will see which recovery model is having for a database.