SQL Server Recovery Model

What is a Recovery model?

Recovery models are designed to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged in SQL Server, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time.

Simple: This recovery model does not backups the log. It reclaims the log space automatically which eliminating the need to manage the transactions log space. Operations which requires transactions log backups are not supported. There are some restrictions that comes with simple recover model. Changes since the most recent backup are unprotected and those changes most be re-done with the event of a disaster.  This recovery can only recover to the end of the backup and following operations are not supported.

• Log Shipping
• Always on or Database mirroring
• Media recover without data loss
• Point-in-time restores

Full: This recovery model requires log backups. No work is lost due to the lost or damaged data file. Can recover to an arbitrary point in time. Normally the work loss exposure is none. If the tail of the log is damaged, changes since the more recent log backup must be re-done. You can recover to a specific point in time, assuming that your backups are complete up to that point in time.

Bulk Logged: This recovery model also requires log backups. An adjunct of the full recovery model that permits high-performance bulk copy operations. Reduces log space by using minimum logging for most bulk operations. If the log is damaged or bulk-logged operations occurred since the most recent log backup, changes since that last backup must be done otherwise, no work is lost. You can recover to the end of any backup. Point-in-time recovery is not supported.

The information presented here is a work from Microsoft not mine! I want my readers, DBA and developer to get familiar with the recovery models. Once you read this article, check your database recovery model, ask your DBA why is it configured simple or full recovery model? Knowing the limitations and the capacity of these recovery models helps the dba and developer in many ways. 

Source:

http://msdn.microsoft.com/en-us/library/ms189275.aspx