Database recovery models are used to control Transaction log maintenance. There are 3 Models namely
- Full
- Simple
- Bulk –logged
Full Recovery Model:
In Full recovery model, all the transactions are logged in the Log file. We can recover a database to point in time recovery, if the database is in Full recovery mode. In Full recovery model, we can restore the individual pages. Log Maintenance is required in this model.
Bulk logged recovery model:
Bulk logged recovery model is almost similar to Full recovery model, only difference is Bulk operations will not be logged in the T-Log. We can change the Full recovery model to Bulk recovery model before the Bulk operations (Recommended method). Log backup is possible in Bulk recovery model. Log Maintenance is required in this model.
Simple Recovery model:
Simple recovery model will not log the transactions in the T-log. So full backup is take at regular intervals for the minimal loss of data. Log Backup is not possible in this recovery model. Log Maintenance is not required in this model.
Changing Recovery Model using T-SQL:
Alter Database Database_name set recovery Recovery_Model
--Example
Alter Database TestDB set recovery Full