Recovery Model In SQL Server

Overview

We all know that data is stored in Transactional log files, which are LDF files. The LDF size grows and you are not able to do anything to it, like at the time of bulk upload in the banks, where that bulk upload takes six hours. Imagine the size of the LDF when it's growing. Let’s see what we can do in order to restrict the size of LDF.

Introduction

SQL Server provides three types of recovery models and they are:

• Full
• Bulk-logged
• Simple

Let’s look one by one and see what do they offer in terms of ever growing LDF size.

Full Recovery Model

As the name implies, Full Recovery model is the one where you keep the full recovery model of the database, it keeps all the data in a transactional log file as it keeps the data in that LDF file until a transactional log backup occurs or the truncating of the LDF file.

When a full model is kept, the data is first captured in a transactional file and then it is written in main file, say the MDF file. The main advantage of using a Full Recovery model is you can recover the point of time of any transactional log file, from where the data is lost. In database terms, it is called RPO and RTO.

When you are synchronizing any database, like production database with Primary and Standby Server, in logical terms, it’s called DC and DR, as they are nothing but Data Crash and Data Recovery.  A point to remember here is that all the databases are kept in Full Mode.

Let’s see with the help of a query and GUI as how we can set Full Recovery model.

• Open SSMS
  
  
  
  
• Select any database.
  
  
  
  
• Select database ->right click ->Properties.
  
  
  
  
• Go to Options.
  
  
  
  Select Mode Full and Click OK.
  
  
• Now lets see, how we can change through Query.
  
  
  
  
• It is Full Recovery model that allows you to have recovery of the database at any point of time. With Full Model, you need to make transactional log backup, otherwise thereis no use of using this model as it will grow.

Advantages

• Transactional log backups are possible in Full Mode.
• Complete Backup is possible.
• Partial backups are possible.
• Differential backups.

I developed an Application which uses Full Mode of the database to be synchronized.

I developed a dashboard instead of login to SQL Server and it checks that the database is in synchronization, as this dashboard gives an idea.



Bulk-Logged Model

Bulk Logged model is generally used when there is a bulk inserted into the database operations and one needs to keep on inserting a file from the Application. These bulk inserts generally take six hours. Now, imagine the LDF size part here and the Bulk Logged model is suitable.

In Bulk Logged model, you can again recover the transactional log files LDF at the point of crash or data loss.

Advantages

Advantages are the same as Full Recovery model.

Let’s see how we can make the database Bulk model Logged.





Now, let's see through a query.



Simple Recovery Model

As the name says Simple Recovery model is suitable where the database is under UAT observation, where you need to restore database from one point to another and so on. Here, in Simple Recovery model, the recovery point object is not possible. Backup of the latest transaction will only give you data and not the RTO time object. The Application data is captured in LOG files and when we shrink LDF files again, the space becomes available for new data.

Advantages

• Complete backups.
• Differential backups.

Lets see, how to make Simple Recovery model.







Conclusion

These were Recovery Models in SQL Server as as they tell  you how you can recover a database, when a disaster occurs or any data loss takes place. I hope this article was helpful. If you have any doubts on this topic feel free to ask.