Understanding SQL Server recovery model

by Ashish Upadhyay

Recovery Model in SQL Server is one of the important aspects of the database configuration, as it determines the way transaction logs are going to maintain. Microsoft has designed the recovery models to control transaction log maintenance. Till the recent version of SQL Server 2012 (RCO) SQL Server has three types of recovery models, simple, full, and bulk-logged.

Generally user database runs on either full recovery model or simple recovery model depending upon your server configuration while by default system database run as:

System Databases	Default Recovery Model	Optional Recovery Model
Master	Simple	Full and Bulk logged recovery model supported
msdb	Simple	Full and Bulk logged recovery model supported
Model	Full	Full and Bulk logged recovery model supported
tempdb	Simple	Full and Bulk logged recovery model is NOT supported

Because user database inherit all the properties or schema from the “model” database obviously recovery model of your database will be same as recovery model of the model database, which you can change later.

To view Recovery Model setting, open the SSMS (SQL Server Management Server), right click on a database, and then select Properties. On the properties dialog box go to “Options” and you can see the Recovery Model in the right. Please refer below image for the same.

Let’s understand all the three recovery models available in SQL Server.

Simple	Full	Bulk logged
In this simple recovery model, transaction log backups are not allowed, that means you cannot setup transaction log backup. This model also automatically reclaims log space and keeps the log space requirement minimal. So there is almost no need to manage the transaction log space means less administrative overheads. Database can be restored only to the point of its last backup (Full and/or differential). All the transactions done after last backup need to be re-done.   As a good practice keep the system database on simple (except tempdb as only full recovery model is allowed for tempdb), keep the read only databases, and/or the databases in DEV, TEST and STAGGING environment on simple recovery model seeing the importance of the data.	The backup of the log file is highly recommended in full recovery model. No work is lost due to a lost or damaged data file and data can be recovered to an arbitrary point in time. Generally no data loss is expected until if the tail of the log is damaged. In this case changes since the most recent transaction log backup must be redone In this model all operations are fully logged, including bulk import operations. As a good practice all the databases in the PRODUCTION Environment should be on the full recovery model with proper transaction log backup. Frequency of the transaction log backup may vary from one environment to other.	Bulk logged recovery model is an adjunct of the full recovery model that permits high-performance bulk copy operations. Because it minimal logging for most of the bulk logged operations for example: INSERT INTO, BCP commands, bulk insert, CREATE INDEX, ALTER INDEX etc., it requires less space for log. Loss of the data can recover to the end of any backup not to a specific point as point-in-time recovery is not supported in this model. As a good practice bulk logged recovery model can be used for heavy data load operation where performance of the operation is more important than recovery of the data. You can set your database to the “bulk Logged” at the start of the operation and re-set back to “simple” or “Full” model once bulk load operation is finish. Make sure to keep a full back up once you re-set the database to back to simple or full recovery model.