Database Recovery Models - 11 Apr 2000

If you have production databases running on SQL Server and you've upgraded to SQL Server 2000 or are considering doing so soon, you'll want to know about one exciting new feature of SQL Server 2000. This new feature, the database recovery model, controls both the speed and the size of your transaction log backups and the degree to which you might be at risk of losing committed transactions in the event of media failure. As a SQL Server 2000 database owner (DBO), you can use the ALTER DATABASE statement to supply one of three values—full, bulk_logged, or simple—for any database's recovery model. These three values replace the database options select into/bulkcopy and trunc. log on chkpt. Those options are also available in SQL Server 2000, but Microsoft intended them only for backward compatibility and they might disappear in a future release. I recommend that you discontinue using them and rely on setting your recovery model instead.

Although I recommend that you determine your database recovery model as soon as possible, your decision isn't permanent. You can change recovery models any time, and I'll describe the ramifications of such changes. But first, let me tell you how the three new models relate to the two database options that were available before SQL Server 2000.

SQL Server usually can back up data in one of three ways: a full database backup, a transaction log backup, or a differential backup. (File and file group backups are irrelevant for this discussion.) In SQL Server 7.0 and earlier releases, if you've turned on the option that allows SELECT INTO and unlogged bulkcopy and you've already performed either of those operations, you can't back up your logs. So if you have scheduled log backup scripts to run at regular intervals, those backups will fail.

Conversely, if you have scripts that execute SELECT INTO operations and you've turned off the option to allow these operations, those scripts will fail. With the new SQL Server 2000 database recovery options, your SELECT INTO and bulk copy/bulk insert scripts will never fail. You'll always be able to perform these operations under any model. And with two of the three models, you can make log backups no matter which operations you've done, and your backup scripts won't fail. The difference between the models is in how much time and space your backups take and how great your risk of data loss is. Let's look now at the three database recovery models.

Full Recovery Model

The full recovery model guarantees the least risk of losing work if a data file is damaged. For a database with this model, SQL Server fully logs all operations, so every row inserted through a bulk copy program (bcp) or BULK INSERT operation is written in its entirety to the transaction log. If you experience a media failure for a database file and need to recover a database that was using the full recovery model, you can restore the database up to any specified time. If your log file is available after the failure, you can restore up to the last transaction committed before the failure. Also, SQL Server 2000 supports a feature called log marks, which lets you place reference points in the transaction log; in the full recovery model, you can choose to recover to a log mark. (SQL Server 2000 Books Online—BOL—contains more details about log marks.)

The full recovery model also fully logs CREATE INDEX operations, unlike earlier SQL Server releases, which log only the fact that an index was created. In SQL Server 2000, the recovery from a transaction log backup that includes index creations is much faster because you don't need to rebuild the index.

The full recovery model sounds great, but it comes with a trade-off. Your transaction logs can be enormous, and consequently, making log backups can take substantially longer than with any previous release.

Bulk_Logged Recovery Model

The bulk_logged recovery model allows for recovery in case of media failure and gives you the best performance using the least log space for certain bulk operations, including BULK INSERT, bcp, CREATE INDEX, WRITETEXT, and UPDATETEXT. In the bulk_logged recovery model, SQL Server minimally logs these operations. When you execute a bulk operation, SQL Server logs only the fact that the operation occurred. However, the operation is fully recoverable because SQL Server keeps track of which extents the bulk operation modified. Every file in a SQL Server 2000 database now has an additional allocation page called a MINLOG page, which SQL Server manages like it does the GAM and SGAM pages. (For more details about these two page types, see Inside SQL Server, "The New Space Management," April 1999.)

Each bit on a MINLOG page represents an extent; a bit value of 1 means that a minimally logged bulk operation has changed the extent since the last full database backup. A MINLOG page is located at the eighth page of every data file and every 256,000 pages thereafter. All the bits on a MINLOG page reset every time a full database backup or log backup occurs.

Because the bulk_logged model minimally logs bulk operations, the operations can happen much faster than under the full recovery model. Setting the bits in the appropriate MINLOG page requires some overhead, but compared with the cost of logging each change to a data or index row, the cost of flipping bits is negligible. If your database is using the bulk_logged model and you haven't performed any bulk operations, you can recover to any point in time or to a named log mark. If you have performed bulk operations, you can recover up to the end of any transaction log backup.

The trade-off comes during the log backup. Besides copying the contents of the transaction log to the backup media, SQL Server scans the MINLOG pages and backs up all the modified extents along with the transaction log. The database's log file stays small, but the log backup can be many times larger. So with the bulk_logged model, the log backup takes more time and can take up considerably more space than in the full recovery model. The time necessary to restore a log backup you made in the bulk_logged recovery model is comparable to the time to restore a log backup you made in the full recovery model. The operations don't need to be redone; the information necessary to recover all data and index structures is available in the log backup.

Simple Recovery Model

The simple recovery model allows for the fastest bulk operations and the simplest backup-and-restore strategy. Under this model, SQL Server truncates the transaction log at regular intervals, removing committed transactions. Thus, only full database backups and differential backups are allowed; you'll get an error if you try to back up the log while using the simple recovery model. Because SQL Server doesn't need the log for backup, it reuses sections as soon as all the transactions in them are committed or rolled back and no longer needed for recovery from server or transaction failure.

Comparison to Database Options

I mentioned that Microsoft intended these recovery models to replace the database options select into/bulkcopy and trunc. log on chkpt. However, in SQL Server 2000, changing either of these options also changes your recovery model, and changing your recovery model changes the value of one or both of these options. The recommended method for changing your database recovery model is using the ALTER DATABASE command:

ALTER DATABASE    SET RECOVERY [FULL | BULK_LOGGED |    SIMPLE]

To see what model your database is using, you can use the new property function DATABASEPROPERTYEX:

SELECT databasepropertyex('', 'recovery')

This command returns one of the three values: full, bulk_logged, or simple. Table 1 shows the relationship between the database options and the new recovery models.

If you're using SQL Server 2000 Standard Edition or Enterprise Edition, the Model database starts in the full recovery model, so all your new databases will use the full model also. If you're using SQL Server Personal Edition or the Microsoft SQL Server Desktop Engine (MSDE—formerly Microsoft Data Engine), the Model database starts in the simple recovery model. You can change the recovery model of the Model database or of any user database by using the ALTER DATABASE command. Or you can change the recovery model by changing the database options. For example, if your database is using the full model and you change the select into/bulkcopy option to true, your database recovery model automatically changes to bulk_logged.

Conversely, if you force the database back into the full model with ALTER DATABASE, the value of the select into/bulkcopy option changes automatically. In fact, the sysdatabases table doesn't record any special information for the recovery model. The status bits for these two database options determine the recovery model. If the 4 bit in sysdatabases.status is set, the database has select into/bulkcopy enabled, and if the 8 bit is set, the database has trunc. log on chkpt. set. Table 1 can help you determine which recovery model a database is using.

Recovery Scenarios

Now let's look at how to recover your database in each of the three models. If your database is using the full recovery model, the log has all the information about every change to your database. If you lose any data files because of media failure, you can still back up the transaction log containing all the work done up to the time of the failure, assuming that you didn't also lose your log files. (Unlike with SQL Server 7.0, you don't need access to your primary data file to back up a transaction log.)

Recovery with the full model starts with a restore of the most recent full database backup, followed by the most recent differential backup. You can then apply all transaction log backups made after the last differential backup (or after the full database backup, if you have no differential backups). The last step is to recover all transactions that were completed by the time of the failure by applying the log backup you made after the failure occurred.

If your database is using the bulk_logged model but you haven't performed any bulk operations, you can restore exactly as if you were in the full recovery model. However, if you have performed bulk operations, a final backup of the last part of the log requires that the data files be available so that you can back up the affected extents along with the transaction log.

Under the bulk_logged model, if you're restoring your database because of a media failure involving the data files, you can't perform that final log backup. You'll start by restoring the most recent full database backup, followed by the most recent differential backup. You can then apply all transaction log backups made after the last differential (or after the full database backup, if you have no differential backups). You can restore only to the last log backup made before the failure.

In the previous two scenarios, if the media failure affected the log files, you can restore to the last log backup made before the failure. In the case of the simple recovery model, you can restore only the most recent full database backup and then the most recent differential backup, if one exists.

Benefits

The new recovery models offer you two major benefits. First, you can always perform a SELECT INTO operation without having to worry about what options you've set. Second, you can freely switch between the full and bulk_logged models without worrying about your backup scripts failing. For example, you might need to switch between models if you usually operate using the full model but occasionally need to perform a bulk operation quickly. You could change the model to bulk_logged and pay the price later when you backed up the log; the backup would just take longer and be larger.

You can't easily switch to and from the simple model, but this limitation isn't a big sacrifice. When using the ALTER DATABASE command to change your recovery model from simple to full or bulk_logged, you must first make a complete database backup for the change in behavior to be complete. The simple recovery model is comparable to the database option that truncates the log on checkpoint operations. The truncation option isn't recommended for production databases, where you need maximum transaction recoverability. The only time the truncate log on checkpoint option is really useful is in test and development situations or for small databases that are primarily read-only. I suggest the same requirements for using the simple recovery model. In your production databases, use the full or bulk_logged models and switch between those models whenever you need to.