A recovery model allows you to control to what extent you are ready to risk losing committed transactions if a database is damaged. It also determines the speed and size of your transaction log backups. Additionally, the choice of a recovery model has an impact on the size of the transaction log and therefore on the time period needed to back up the log. The Database Engine supports three recovery models:
- Full
- Bulk-logged
- Simple
The following sections describe these recovery models.
Full Recovery Model
During full recovery, all operations are written to the transaction log. Therefore, this model provides complete protection against media failure. This means that you can restore your database up to the last committed transaction that is stored in the log file. Additionally, you can recover data to any point in time (prior to the point of failure). To guarantee this, such operations as SELECT INTO and the execution of the bcp utility are fully logged too.
Besides point-in-time recovery, the full recovery model allows you also to recover to a log mark. Log marks correspond to a specific transaction and are inserted only if the transaction commits.
The full recovery model also logs all operations concerning the CREATE INDEX statement, implying that the process of data recovery now includes the restoration of index creations. That way, the re-creation of the indices is faster, because you do not have to rebuild them separately.
The disadvantage of this recovery model is that the corresponding transaction log may be very voluminous and the files on the disk containing the log will be filled up very quickly. Also, for such a voluminous log, you will need significantly more time for backup.
Note – If you use the full recovery model, the transaction log must be protected from media failure. For this reason, using RAID 1 to protect transaction logs is strongly recommended. (RAID 1 is explained in the section “Using RAID Technology” later in this chapter.)
Bulk-Logged Recovery Model
Bulk-logged recovery supports log backups by using minimal space in the transaction log for certain large-scale or bulk operations. The logging of the following operations is minimal and cannot be controlled on an operation-by-operation basis:
- SELECT INTO
- CREATE INDEX (including indexed views)
- bcp utility and BULK INSERT
Although bulk operations are not fully logged, you do not have to perform a full database backup after the completion of such an operation. During bulk-logged recovery, transaction log backups contain both the log and the results of a bulk operation. This simplifies the transition between full and bulk-logged recovery models.
The bulk-logged recovery model allows you to recover a database to the end of a transaction log backup (that is, up to the last committed transaction). Additionally, you can restore your database to any point in time if you haven’t performed any bulk operations. The same is true for the restore operation to a named log mark.
The advantage of the bulk-logged recovery model is that bulk operations are performed much faster than under the full recovery model, because they are not fully logged. On the other side, the Database Engine backs up all the modified extents, together with the log itself. Therefore, the log backup needs a lot more space than in the case of the full recovery. (The time to restore a log backup is significantly increased, too.)
Simple Recovery Model
In the simple recovery model, the transaction log is truncated whenever a checkpoint occurs. Therefore, you can recover a damaged database only by using the full database backup or the differential backup, because they do not require log backups. Backup strategy for this model is very simple: restore the database using existing database backups and, if differential backups exist, apply the most recent one.
Note – The simple recovery model doesn’t mean that there is no logging at all. The log content won’t be used for backup purposes, but it is used at the checkpoint time, where all the transactions in the log are committed or rolled back.
The advantages of the simple recovery model are that the performance of all bulk operations is very high and requirements for the log space are very small. On the other hand, this model requires the most manual work because all changes since the most recent database (or differential) backup must be redone. Point-in-time and page restore are not allowed with this recovery model. Also, file restore is available only for read-only secondary filegroups.
Note – Do not use the simple recovery model for production databases.
Changing and Editing a Recovery Model
You can change the recovery model by using the RECOVERY option of the ALTER DATABASE statement. The part of the syntax of the ALTER DATABASE statement concerning recovery models is
SET RECOVERY [FULL | BULK_LOGGED | SIMPLE]Code language: SQL (Structured Query Language) (sql)There are two ways in which you can edit the current recovery model of your database:
- Using the databasepropertyex property function
- Using the sys.databases catalog view
If you want to display the current model of your database, use the recovery value for the second parameter of the databaseproperty function. Example 16.2 shows the query that displays the recovery model for the sample database. (The function displays one of the values FULL, BULK_LOGGED, or SIMPLE.)
Example 16.2
SELECT databasepropertyex('sample', 'recovery')Code language: SQL (Structured Query Language) (sql)The recovery_model_desc column of the sys.databases catalog view displays the same information as the databasepropertyex function, as Example 16.3 shows.
Example 16.3
SELECT name, database_id, recovery_model_desc AS model
FROM sys.databases
WHERE name = 'sample'Code language: SQL (Structured Query Language) (sql)The result is
