Recovery model

Every database has a property called recovery model. The recovery model determines how transactions are logged, and for how long time committed transactions will be stored in the transaction log. The recovery model is set by T-SQL command, ALTER DATABASE:

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

The recovery model has three possible options:

• SIMPLE
• BULK_LOGGED
• FULL

When the recovery model is set to SIMPLE, SQL Server clears transactions from the transaction log when they are checkpoint. This approach leads to a relatively small transaction log file, which seems to be a good behavior, but, on the other hand, the transaction log does not hold transaction descriptions 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.

As an example for a database where content could be reloaded repeatedly, we can imagine data warehouse for statistical purposes loaded periodically from an operational database such as accounting, order processing, or production tracking.

When recovery model is set to SIMPLE, SQL Server keeps transaction log records in the transaction log file to the checkpoint only. When the recovery model set to FULL, SQL server keeps transaction log records in the transaction log file until BACKUP LOG statement is executed. It provides the ability for complex and sophisticated backup strategies. With recovery model set to full, SQL Server keeps all transaction records indefinitely until the transaction log file is full, then the database stops its work and becomes inaccessible. That's why we need to back up the transaction log regularly because the backup clears the transaction log, keeps it a manageable size, and defends the database against stopping work. 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.

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 doesn't provide the ability to restore the database at any point in time. It is used only on databases where some small data loss is allowed and one example of usage can be as follows:

• Before periodical data load, set recovery model to BULK_LOGGED
• Load flat files, images, or other LOBs
• Set the recovery model back to full
• Back up the database