Recovery Model of MS SQL Server database

Check the Recovery Model of MS SQL Server Databases:

SELECT name AS [Database Name],recovery_model_desc AS [Recovery Model] FROM sys.databases;

SELECT 'ADVENTUREWORKS' AS [Database Name],DATABASEPROPERTYEX('ADVENTUREWORKS', 'RECOVERY') AS [Recovery Model];


Change the Recovery Mode with following command:

ALTER DATABASE dbName SET RECOVERY recoveryOption

Example:
ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE


Option of RECOVER MODEL:
1. SIMPLE
2. BULK-LOGGED
3. FULL

SIMPLE Recovery Mode:
SQL Server maintains only a minimal amount of information in the transaction log. It can clean transaction log time to time. You can restore full or differential backups only. It is not possible to restore such a database to a given point in time. You can lost changes which you made after backup.

BULK-LOGGED Mode:
The bulk-logged model records these operations in the transaction log using a technique known as minimal logging.It will create minimal log of large transaction. It operate like full recovery mode. This saves significantly on processing time, but prevents you from using the point-in-time restore option.

Full Mode:
This Mode ensure the data is not lost that includes a combination of full and differential database backups in conjunction with transaction log backups.It allow you to restore point in time recovery with transaction log backup.