Recovery models

             What is Recovery Model? Benefits of Recovery Models?

A recovery model is a Database property that control the basic behavior of the backup and restore operations for a DB. Recovery models are designed to control transaction log maintenance.

There are three recovery models that can be set on each user database which determines the types of backups you’ll use. 

To find the recovery model for databases use below command :

SELECT name, recovery_model_desc FROM sys.databases

We can set the recovery model via the GUI or use the ALTER DATABASE command :

•          ALTER DATABASE SALES SET RECOVERY FULL
•          ALTER DATABASE SALES SET RECOVERY SIMPLE
•          ALTER DATABASE SALES SET RECOVERY BULK_LOGGED

FULL Recovery Model :

In full recovery you must run a log backup on a regular basis in order to reclaim log space. Recovery to a point in time is fully supported. For any production system that has data that is very important to the business,then the database recovery model should be full recovery .

BULK_LOGGED Recovery Model :

This recovery model reduces the size of the transaction log by minimally logging some operations such as bulk inserts. The problem is, recovery to a point in time is not possible with this recovery model because any minimally logged operations cannot be restored. This means that bulk-logged has all the overhead of Full Recovery, but effectively works like Simple Recovery. Unless you have specific needs or are willing to perform lots of extra backup operations, it’s not recommended to use bulk-logged recovery.

SIMPLE :

In this mode, the transaction are removed automatically at each checkpoint within the database and no log backups are possible. Recovery to a point in time is not possible and you could lose  data under simple recovery model. This Simple recovery model is not advised for production databases which are critical.

Backup History :

The following commands provides information of history to the backups in the MSDB database.

Use msdb
go
SELECT * FROM dbo.backupfile  - Contains one row for each data or log file that is backed up
SELECT * FROM dbo.backupmediafamily  - Contains one row for each media family
SELECT * FROM dbo.backupmediaset - Contains one row for each backup media set
SELECT * FROM dbo.backupset  - Contains a row for each backup set
SELECT * FROM dbo.backupfilegroup - Contains one row for each file group in a database at the time of backup


Backup File Information :

We have three basic commands to check backup file information which are  given below:


RESTORE VERIFYONLY FROM DISK = N'c:\backups\DBbak'
RESTORE HEADERONLY FROM DISK = N'c:\backups\DB.bak'
RESTORE FILELISTONLY FROM DISK = N'c:\backups\DB.bak'