We all are aware that Transaction log is the heart of any database and needs to be taken care of it every time. But unfortunately if we lose transaction log, that will be the end of the story 😉 So in any DBMS, there should be a mechanism to keep the transaction log safe. That is nothing but Recovery model. Recovery model is the mechanism in SQL Server to control the transaction logging and also takes care of whether transaction log requires back up or not. This mechanism mainly helps us in recovering our data from disasters. There are 3 types of recovery models available in SQL Server namely Full, Simple and Bulk logged recovery models.
Types of Database recovery models:
- Full Recovery model — It can recover to any point in time. Data will not be lost.
- Simple Recovery model — It can recover only to the end of an existing back up. Changes made in the data after the most recent back up will be lost.
- Bulk Logged Recovery model — It can recover to the end of any backup. If the log is damaged occurred after the most recent log backup, the changes after that last backup must be redone.