Recovery Models

The recovery model, a new option in SQL Server 2000, determines what level you'll be able to recover your database and recently entered data to in the event of a system failure. To set a recovery model, right-click the database in Enterprise Manager, select Properties, then select the Options tab. Select the appropriate recovery model, then close the dialog box.

The Full recovery model lets you recover all the data up to the point of failure—unless the log is damaged. In the case of log damage, any data entered since the last log backup will have to be reentered. If a data file is damaged, you can still recover all committed transactions. If you implement Full recovery, SQL Server logs all changes to the database, including operations such as SELECT INTO and bulk data loads, which usually are performed as nonlogged operations. Although this recovery mode is the safest, it can add considerable overhead to your SQL Server.

The Simple recovery model is the opposite extreme. This model takes the place of the trunc. log on checkpoint database option that exists in SQL Server 7.0 and earlier. At a checkpoint, after writing the data to the database, SQL Server removes the committed transactions from the transaction log. With this recovery model, your log is unlikely to fill up; however, you can't perform transaction-log backups or file or filegroup backups, which require the log. You must use full backups and differential backups. Consequently, you run the risk of losing all data entered since the last backup. This recovery model isn't a good one to use for production systems.

The Bulk-Logged recovery model is a compromise. This model allows transaction-log backups, so you can recover up to the point where the system failed, with a few restrictions. Bulk-Logged means that when you perform an operation, such as a bulk data load, SQL Server doesn't log each row you inserted. Instead, it logs references to the extents that contain those rows. When the transaction-log backup runs, SQL Server backs up the log and uses these references to back up the inserted data from the data file. But if you lose the data file and you've performed some bulk-logged operations, you can't back up the log that's on the disk; so, you might have to reenter some data.

Switching between Full and Bulk-Logged recovery models is easy, so choose the model that works best for you now and consider switching when the situation warrants it. (For more information about how to use the recovery models, see Kalen Delaney, Inside SQL Server, "Database Recovery Models," June 2000.)