SQL SERVER Recovery Models

The recovery model works on the DB level and gave us the privilege to set the Database transaction logged mechanism. These transactions will save in sequence based on the recovery model. SQL SERVER provides three types of recovery models, Simple, Full, and bulk-logged. Due to this DBA ensures a Database recovery point and saves the organization in case of any Media failure. By default, SQL SERVER provides a simple recovery model in the Database. You can change the recovery model at any time as per requirement.

Now Below I am explaining all recovery models in detail.

1- Simple Recovery Model

The word “Simple” itself shows that the recovery model uses very few system resources Hence, the Simple recovery model in SQL SERVER is the most basic. Whenever transactions perform on SQL SERVER, it will write on Logfile until it’s committed, and data has been written to datafile then log file ready to overwrite/reuse its space for new transactions. With this, model SQL SERVER not able to perform Point in Time Recovery of a particular database.

Advantages

  • Whenever a checkpoint occurs transaction log truncated.
  • Very minimal administration work required, and disk space consumption is very low.

Disadvantages

  • The point in time recovery is not possible due to the inability of log backups.
  • The DB can be restored only if full or Diff DB backups maintain.

2- Full Recovery Model

The Recovery Model which gave SQL SERVER DB full protection in all manners is called the “Full” recovery model. Normally production environments set DB in full recovery mode to protect their data in case of media failure and ensure point-in-time recovery. In the Full recovery model, all transaction first writes in the LOG file, and then data written in datafiles. Transactional log backups need to be configured to control the size of the log file on the other hand It will grow gradually.

Advantages

  • One of the biggest benefits of the full recovery model is that it gave point in time recovery in case of media failure.

Disadvantages

  • With the use of the Full recovery model, You need to plan and schedule transaction log backups, otherwise, the growth of transaction logs will increase quickly.
  • It consumes Disk space because it captures every transaction.

3- Bulk-Logged Recovery Model

A BULK Recovery model is the combination of the full and Simple recovery model.

If your database has a Bulk Recovery Model and SQL Server performs any bulk operation like BCP, Insert INTO, Bulk Insert, etc performed then a very minimal log space use and the response of operation will be very fast and ensure SQL Server performance, it same as Simple Recovery model. When the Bulk operation finish then It acts as a full recovery model. So, point in time Recovery does not guarantee.

Advantages

  • Minimal logging will apply in the case of bulk operations.
  • Space consumption is very low during bulk operations.

Disadvantages

  • Chances of data loss more in the case of failure

Leave a Comment