Recovery model is our way to instruct SQL Server how to maintain the log file.
We have 3 recovery models in SQL Server – SIMPLE, BULK_LOGGED and FULL.
We’ll review them and describe the differences between them.
SIMPLE recovery model
When the database recovery model is set to SIMPLE, every DML and DDL statement is written to the transaction log.It is done in order to support the basic recovery ability; For example, long operation being cancelled due to user request or server shutdown. In this case, SQL Server will rollback the unfinished operation based on log records in the log file and will keep our database consistence. Log records are cleaned / flushed after the transaction has been committed and checkpoint occurred.
BULK_LOGGED recovery model
When the database recovery model is set to BULK_LOGGED, it acts exactly like as the SIMPLE recovery mode, except the log cleaning. Log records are written into the log file and transferred to the data files in same way as in SIMPLE recovery model (Checkpoint, Lazy writer and Eager writer); however these records are not cleaned out of the log file automatically. Log records will stay inside the log file until “BACKUP LOG” command executed. Log backups will provide us additional recovery/restore options to the existing in SIMPLE recovery model.
FULL recovery model
When the database recovery model is set to FULL, all aspects of log cleaning work are functioning like in the BULK_LOGGED recovery model. There are 2 major differences in recovery options:
Things we need to remember: