recovery models in sql server

 

Recovery models in SQL server that enable you to determine the way SQL Server manage the log files. And ready your DB for recovery after information loss or any other issue. Each of these speaks to an alternate way to deal with adjusting the trade-off. Between protecting disk space and granular disaster recovery options.

  • Simple
  • Full
  • Bulk-logged

Simple RECOVERY MODEL

SQL Server keeps up a minimal measure of data in the transaction log. SQL Server removes the txn log each time the DB achieves a txn checkpoint. leaving no log sections for disaster recovery uses.

By using this model, we able to recover the full or differential backups only. It doesn’t achieve to restore such a database to a given moment. — you can restore it to the correct time when a full or differential backup happened. Hence, you will lose any information changes set aside. A few minutes of the latest full/differential backup and the time of the fail.

FULL RECOVERY MODEL

With this model, SQL Server cares the Txn log until you back it up.
This enables you to draw a disaster recovery plan. That contain both Full & differential backups in conjunction with txn log backups.

In case of a database failure, you have to restore databases using full recovery. Also to protect the data changes saved in txn log files. the full recovery model enables you to restore a DB to a particular moment.

For ex: if an incorrect alteration corrupted your information at 4:36 p.m. on Monday. You could use SQL Server’s point-in-time restore to rollback your database to 4:35 p.m.

Bulk LOGGED RECOVERY MODEL

The bulk-logged recovery model is same as full recovery model. The main difference is it can handle the bulk changes done in databases. The bulk-logged model records these operations in the txn log. Utilizing a method known as minimal logging. Yet keeps you from utilizing the point-in-time restore option.

Microsoft prescribes that the bulk-logged recovery model utilized for brief time-frames. Best practice directs that you change a database to the bulk-logged recovery model. Before leading mass operations and restore it to the full recovery model. When those operations finish.

CHANGING RECOVERY MODELS in SQL Server

  • Open the SQL server
  • Select the database:
    Expand Databases, select user DB or Sys DB.
  • Open the Database Properties:
    Right-tap the database, and after that click on Properties.
  • View the present Recovery Model:
    In the Select, a page sheet, click on Options to see the present Recovery model.
  • Select the new Recovery Model:
    Select either Full, Bulk-logged, or Simple.
  • click OK.

Leave a comment