A recovery model setting determines how much data can be recovered after a database has crashed. Each database can have its own recovery model, based on how much data loss you can tolerate. To set the recovery model for a database in SQL Server Management Studio (SSMS), you highlight the database, right-click to open the Properties box, select the Options page, and select the recovery model from the drop-down list.
The three types of recovery models are full, simple, and bulk-logged. The full recovery model makes the most use of the transaction log and lets you recover a database to a highly granular point in time. All operations (e.g., data transactions, structural changes to the database, operational instructions such as commits and rollbacks, large object—LOB—and bulk operations) are captured in the log. The transaction log continues to grow until it’s backed up by a transaction log backup operation.
The simple recovery model makes the least use of the transaction log and lets you recover to your last good full database backup. As in the full recovery model, all transactions (except some bulk operations) are captured in the log. Unlike in the full recovery model, SQL Server automatically rids the log of inactive entries. Therefore, you can’t make backups of the transaction log for a database using the simple recovery model.
Between the extremes of full recovery and simple recovery is the bulk-logged recovery model. Although the name might lead you to believe that bulk operations are logged, they’re not. During a bulk operation, which is often a high-speed insert of numerous records, SQL Server sets a bit on each database extent that’s changed by the bulk operation, but the actual inserted records aren’t written to the transaction log. During the next transaction log backup, SQL Server checks for the bit flags and directs the transaction log backup to include the actual database extents that were modified during the bulk operation, in addition to the normal inserts and deletes that a transaction log backup regularly captures. The bulk-logged recovery model log backup contains the results of a bulk-logged operation, not the actual, individual transactions.
Using the bulk-logged recovery model gives you the completeness of the full recovery model without the overhead of logging all the bulk inserts. However, using the bulk-logged recovery model does have some associated risks. If you lose the source data for the bulk operations between log backups, you can’t fully recover your database. Also, you can’t restore to a point in time on a log backup that includes bulk-logged operations—the restore operation will fail.
Although using a full or bulk-logged recovery model means more activity to the transaction log file and a larger (and longer) log file backup, the tradeoff is less data lost following a database crash. For a full discussion of recovery models, see “Recovery Models,” http://www.sqlmag.com, InstantDoc ID 21702.