Recovery Models in SQL Server

A recovery model is a database configuration option that controls how transactions are logged, whether the transaction log is backed up, and what restore options are available for the database. The recovery model you choose for your database has both data- recovery implication and performance implications, based on the logging the recovery model performs or doesn’t perform.

Types of Recovery Models

SQL Server provides three recovery models for data base: Full, Simple and Bulk logged. These models determine how SQL Server works with the transaction log and selects the operation that it logs and whether it truncates the log. Truncating the transaction log is the process of removing committed transactions and leaving log space to new transaction. The following is a definition of each recovery model

• In the Full recovery model, the database engine logs all operations onto the transaction log, and the database engine never truncates the log. The Full recovery model lets you restore a database to the point of.
• In the Simple recovery model, the database engine minimally logs most operations and truncates the transaction log after each checkpoint. In the simple recovery model, you cannot back up or restore the transaction log. Furthermore, you cannot restore individual data page.
• In the Bulk-Logged recovery model, the database engine minimally logs bulk operations such as SELECT INTO and BULK LINSERT. In this recovery model, if a log backup contains any bulk operation, you can restore the database to the end of the log backup, not to a point in time. The Bulk-Logged recovery model is intended to be used only during large bulk operation.