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.