10.2. Recovery models and data loss exposure

When a database is created, the recovery model is inherited from the model database. You can modify the recovery model in Management Studio or use the ALTER DATABASE statement, as shown here:

-- Set the Recovery Model to BULK_LOGGED
ALTER DATABASE [ADVENTUREWORKS2008]
SET RECOVERY BULK_LOGGED

There are three different recovery models: simple, full, and bulk logged.

10.2.1. Simple recovery model

A database in the simple recovery model will automatically truncate (remove) committed transactions from the log at each checkpoint operation. As a result, no transaction log backups are required in limiting the growth of the log, so maintenance operations are simplified.

Despite the reduction in maintenance overhead, the major downside of the simple recovery model is the inability to recover a database to a point in time. As such, the only recovery options are to recover to the previous full or differential backup. This strategy may lead to significant data loss depending on the amount of change since the last full/differential backup.

The simple recovery model is typically used in development and test environments where recovering to the last full or differential backup is acceptable. In such environments, the potential for some data loss is accepted in return for reduced maintenance requirements by avoiding the need to execute and store transaction log backups.

Simple logging vs. no logging Don't confuse the simple recovery model for the (nonexistent) no logging model. Regardless of the recovery model, transactions are logged by SQL Server in order to maintain database integrity in the event of a transaction rollback or sudden server shutdown.

Finally, long-running transactions can still cause significant growth in the transaction log of databases in the simple recovery model. Log records generated by an incomplete transaction can't be removed, nor can any completed transactions that started after the oldest open transaction. For example, in figure 10.5, even though transaction D has completed, it can't be removed as it started after the incomplete transaction C.

The simple recovery model has further downsides: features such as transaction log shipping, covered in the next chapter, can't be used with this recovery model. For compatibility with SQL Server's full feature set and to minimize data loss, we use the full recovery model.

10.2.2. Full recovery model

A database in the full recovery model will log and retain all transactions in the transaction log until the log is backed up, at which point committed transactions will be removed from the log, subject to the same rule that we saw in figure 10.5. Regular transaction log backups are crucial in limiting the growth of the transaction log in a database in the full recovery model.

As well as recording update, insert, and delete statements, the full recovery model will record index creation and maintenance operations, Bulk Copy Process (BCP) commands, and bulk inserts. As a result, the size of transaction logs (and therefore the backup time) can grow very quickly with the full recovery model, and is therefore an important consideration when using log shipping and/or mirroring. We'll cover this in more detail in later chapters when we address index maintenance techniques.

Disaster recovery plan A good disaster recovery (DR) plan considers a wide variety of potential disasters, from small events such as corrupted log files and accidentally dropping a table, right through to large environmental disasters such as fires and earthquakes. A crucial component of any DR plan is a well-documented and well-understood backup and restore plan. Perhaps the best way to validate a DR plan is to simulate various disasters on a random/unannounced basis, similar to a fire drill, with each DBA talking it in turns to practice the recovery process. Not only will this ensure documentation is up to date and well understood by all team members, it will liven up the day, and add some competitive spark to the DBA team!

A common technique used when bulk-loading data into a database in the full recovery model is to switch the database to the Bulk-_Logged model, discussed next, prior to the load.

10.2.3. Bulk_Logged recovery model

When performing large bulk-load operations into a database in the full recovery model, each data and index record modified by the bulk-load process is logged by SQL Server. For very large loads, this can have a significant impact on the load performance.

Under the Bulk_Logged model, SQL Server uses a Bulk Changed Map (BCM) to record which extents^[] the load modified. Unlike the full recovery model, the individual records affected by the bulk load aren't logged. As a result, bulk loads can be significantly quicker than under the full recovery model.

^[] An extent is a collection of eight 8K pages.

The trade-offs of the Bulk_Logged model are significant: when a transaction log backup occurs after a bulk-load operation, SQL Server includes the entire contents of each extent touched by the bulk-load process, even if only a small portion of the extent was actually modified. As a result, the transaction log backup size can be massive, potentially almost as big as the entire database, depending on the amount of modified extents.

The other downside to this recovery model is the inability to restore a transaction log containing bulk-load operations to a point in time. Given these limitations, it's generally recommended that the Bulk_Logged model be used as a temporary setting for the period of the bulk load before switching back to the full recovery model. Making a transaction log backup before and after entering and leaving the Bulk_Logged model will ensure maximum data protection for point-in-time restores while also benefiting from increased performance during the bulk-load operation(s).

Before looking at the backup recovery process in more detail, let's consider some additional backup options at our disposal.