SQL Recovery Model- Microsoft SQL Database Recovery Architecture

There are three recovery models proposed by the Microsoft for recovery of the MS SQL Server database. Depending upon the nature of the database and budget allotment for securing it, the company may choose to apply any of these models for securing the concerned database and to.

1) Simple Recovery Model

2) Full Recovery Model

3) Bulk-logged Recovery Model

We will understand them in detail but before that one need to assert the situation by answering some critical question, they are:-

1) How much the concerned database is critical for the organization and

2) To what extent they are ready to go to secure it.

One’s you conclude that you can now easily proceed to the next logical step i.e. which recovery model is to be followed.  Here I am elaborating each of them with their definition, pros and their cons.

Simple Recovery Model :- As the name implies, the application will make a full backup of the existing database and the user will be able to retrieve and recover from the database crash by simply deploying the database.

Pros - Simplest of all and easy to manage.
Cons- Greater exposure of data from getting lost since all unsaved content will not be available for retrieval.

Syntax for applying SRM:- 

 public void

SqlBackup(

        Server srv

)

Full Recovery Model:- in this model  there is a lesser exposure of the data from getting lost since the backup not only contain the files of the database but also contains transitional backup files which act as intermediate and hence you are able to retrieve more database as compared to Simple database Recovery Model.

Pros:-

More security for the database.

Less exposure of the data from getting lost during database crash.
Cons:-

Highly voluminous.

Takes more time for restoration.

BACKUP DATABASE AdventureWorks2008R2

    TO DISK = 'Z:\SQLServerBackups\AdventureWorks2008R2SimpleRM.bak'

    WITH FORMAT;

GO

Bulk-logged Recovery Model: - It is quite similar to the full Recovery model the difference being that the backup process in it is much faster as compared to the full recovery.

Pros-

Faster process with respect to Full Recovery.

Easier to deploy.
Cons

Increase risk of data loss.

The user can make a choice by taking into consideration what is the requirement and how much extra mile he is willing to go to protect its database. And when sometime tragedy struck the user and the  backup database files got corrupted the user can take the help of professional software which are able to Repair SQL Server Backup files and will give your database back in normal working condition.


Check out this blog if you wish to know further how to Take Backup in MS SQL server. The blog nicely explains how to take backup in MS SQL Server Application.