SQL Server recovery models

Each database hosted by SQL Server contains a property called a recovery model. This property basically affects which backup strategy can be designed. This short section briefly explains recovery models.

How to configure a database's recovery model property

Every database has a property called the recovery model. The recovery model determines how transactions are logged, and for what timespan the transactions will be stored in the transaction log. The recovery model is set by the ALTER DATABASE TSQL command:

-- setting full recovery model ALTER DATABASE <database_name> SET RECOVERY FULL

The recovery model has three possible options:

• SIMPLE
• BULK_LOGGED
• FULL

We'll take a look at these now.

Using the SIMPLE recovery model

When the recovery model is set to SIMPLE, SQL Server clears transactions from the transaction log at every checkpoint. This approach leads to a relatively small transaction log file, which seems to be a good behavior. On the other hand, the transaction log does not hold transaction records, so we are not able to use more sophisticated strategies to minimize data loss.

The SIMPLE recovery model is a good option when data stored in the database is not mission-critical, or when potential data loss is not critical for users, or for databases that could be reloaded from other sources. As an example of a database where data is not crucial for business, we can imagine development databases where data is sometimes damaged intentionally.

Another example of a SIMPLE recovery model could be a database whose content is loaded repeatedly. We can imagine a data warehouse for statistical purposes being loaded periodically from an operational database such as accounting, order processing, or production tracking.

Using the FULL recovery model

When the recovery model is set to SIMPLE, SQL Server keeps transaction log records in the transaction log file up until the checkpoint only. When the recovery model is set to FULL, SQL Server keeps the transaction log records in the transaction log file until the BACKUP LOG statement is executed. It allows complex and sophisticated backup strategies. With the recovery model set to FULL, SQL Server keeps all transaction records indefinitely until the transaction log file is full; then, the database stops working and becomes inaccessible. That is why we need to back up the transaction log regularly as it clears the transaction log, keeps it to a manageable size, and defends the database against it not functioning. As an advanced point, we have to say that when the recovery model is set to FULL, we can restore the database at any point in time.

Using the BULK_LOGGED recovery model

What is the BULK_LOGGED recovery model for? This option has almost the same behavior as a full recovery model, but bulk-logged operations (for example, BULK INSERT of flat files into database tables) are described briefly in the transaction log file. The BULK_LOGGED recovery model does not allow us to restore the database at any point in time. It is used only on databases where some small data loss is allowed. One example of its usage can be as follows:

1. Before periodical data load, set the recovery model to BULK_LOGGED
2. Load flat files, images, or other LOBs
3. Set the recovery model back to FULL
4. Back up the database

Even if this section is very short, it is very important, and we will recall information from here throughout the rest of this chapter. We will work with the recovery model immediately in the following section to successfully back up our databases.