SQL Recovery Model Overview (Simple vs Full Recovery)

Simple Recovery Model

--------------------------------------------------------------------------------
The simple recovery model minimizes administrative overhead for the transaction log, because the transaction log is not backed up. The simple recovery model risks significant work-loss exposure if the database is damaged. Data is recoverable only to the most recent backup of the lost data. Therefore, under the simple recovery model, the backup intervals should be short enough to prevent the loss of significant amounts of data. However, the intervals should be long enough to keep the backup overhead from affecting production work. Including differential backups in the backup strategy can help reduce the overhead.

Generally, for a user database, the simple recovery model is useful for test and development databases or for databases containing mostly read-only data, such as a data warehouse. The simple recovery model is inappropriate for production systems where loss of recent changes is unacceptable. In such cases, we recommend using the full recovery model.

For more information, see Backup Under the Simple Recovery Model.
http://msdn.microsoft.com/en-us/library/ms191164.aspx

Full Recovery and Bulk-Logged Recovery Models

--------------------------------------------------------------------------------

The full recovery and bulk-logged recovery models provide greater protection for data than the simple recovery model. These recovery models rely on backing up the transaction log to provide full recoverability and to prevent work loss in the broadest range of failure scenarios.

•Full recovery model

Provides the normal database maintenance model for databases where durability of transactions is necessary.

Log backups are required. This model fully logs all transactions and retains the transaction log records until after they are backed up. The full recovery model allows a database to be recovered to the point of failure, assuming that the tail of the log can be backed up after the failure. The full recovery model also supports restoring individual data pages.

For more information, see Backup Under the Full Recovery Model.
http://msdn.microsoft.com/en-us/library/ms190217.aspx


•Bulk-logged recovery model

This recovery model bulk logs most bulk operations. It is intended solely as an adjunct to the full recovery model. For certain large-scale bulk operations such as bulk import or index creation, switching temporarily to the bulk-logged recovery model increases performance and reduces log space consumption. Log backups are still required. Like the full recovery model, the bulk-logged recovery model retains transaction log records until after they are backed up. The tradeoffs are bigger log backups and increased work-loss exposure because the bulk-logged recovery model does not support point-in-time recovery. For more information, see Backup Under the Bulk-Logged Recovery Model http://msdn.microsoft.com/en-us/library/ms190692.aspx and Considerations for Switching from the Full or Bulk-Logged Recovery Model. http://msdn.microsoft.com/en-us/library/ms190203.aspx

For more details & comparison table please refer link below:


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

How to stop the transaction log of a SQL Server database from growing unexpectedly

http://support.microsoft.com/kb/873235

A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server

http://support.microsoft.com/kb/317375

SQL Server 2008 Transaction Log Truncation

http://technet.microsoft.com/en-us/library/ms189085.aspx