Category: Recovery Models

In the full recovery model SQL Server does not truncate committed transactions until they have been backed up. It allows creating full, differential and transaction log backups thus making point-in-time recovery possible.

You need to keep in mind that in the full recovery model the transaction log file would be much larger and you have to maker regular log backups to keep it small.

Here are some tips when the full recovery model is better to use:

• There is a big necessity to recover all data
• The database contains some filegroups and there is the need to restore of read/write secondary filegroups and, optionally, read-only filegroups one by one
• There is a necessity of point-in-time recovery
• There is a necessity of individual pages restore

Let’s consider the following scenario:

In the scenario above the failure occurs at 19:00, meaning that the changes made within the period from 18:59 to 21:00 will be lost. The best way out in these circumstances is to restore the full backup (10:00) then the differential backup (16:00) plus transaction log backups (18:00 and 20:00). The transaction log backup that was made at 20:00 allows to roll back to as far as 18:59.

Here is the sequence of commands for restoring this database to 18:59:59:

RESTORE DATABASE your_database FROM DISK = 'full_10_00.bak' WITH NORECOVERY, REPLACE
RESTORE DATABASE your_database FROM DISK = 'diff_16_00.bak' WITH NORECOVERY
RESTORE LOG your_database FROM DISK = 'log_18_00.bak' WITH NORECOVERY
RESTORE LOG your_database FROM DISK = 'log_20_00.bak' WITH STOPAT = '2015-11-19 18:59:59.000', RECOVERY

This is how you can set Full recovery model:

ALTER DATABASE your_database SET RECOVERY FULL

The simple recovery model allows full and differential database backups only and there is no chance of making transaction log backup. At the time when a checkpoint is created in simple recovery model, all committed transactions are removed from transaction log. That means that point-in-time recovery isn’t possible.

So in which cases is it better to use simple recovery model? There are four of them:

• There is no need in backing up transaction logs
• There is no need in point-in-time recovery
• Losing some data is perfectly fine
• Database changes are infrequent

Suppose full and differential backups are regularly created:
As seen from the picture above, the full database backup was made at 10:00 and 22:00 and the differential backup was made at 16:00. Assume that some crucial data were deleted at 19:00. The best thing that is possible to do is to restore the database as of 16:00. First you need to restore the full backup (from 10:00) and then followed by the differential backup (from 16:00). In this case, all changes that were made within the time period from 16:00 to 22:00 will be lost.

The database can be restored in the following way:

RESTORE DATABASE your_database FROM DISK = 'full_10_00.bak' WITH NORECOVERY, REPLACE
RESTORE DATABASE your_database FROM DISK = 'diff_16_00.bak' WITH RECOVERY

This is how you can change your database recovery model to Simple:

ALTER DATABASE your_database SET RECOVERY SIMPLE

The bulk-logged recovery model is designed for intermittent use to improve the performance of bulk imports of large amounts of data. It’s practically the same as the full recovery model with the only exception that under the bulk-logged recovery model some operations are logged minimally. These operations are:

• Bulk import operations (bcp, INSERT … SELECT, and BULK INSERT).
• SELECT INTO operations.
• Partial updates to large value data types using the WRITE clause in the UPDATE statement when inserting or appending new data. Please note that minimal logging is not used when existing values are updated.
• Inserting or appending new data into the text, ntext, and image data type columns using the WRITETEXT and UPDATETEXT statements. Please note that minimal logging is not used when existing values are updated.
• If the database recovery model is set to either bulk-logged or simple, some INDEX DDL operations are minimally logged regardless of whether the operation is executed online or offline. (Minimally logged index operations are as follows: CREATE INDEX, ALTER INDEX REBUILD or DBCC DBREINDEX and DROP INDEX).

Though the bulk-logged recovery model reduces log space usage by using minimal logging for most bulk-logged operations, it is recommended to temporarily switch to bulk-logged recovery model right before performing these operations and then immediately switch back to the full recovery model. This allows to minimize the use of bulk-logged recovery model.

There is the risk of data loss for these bulk-copy operations because bulk-logging operations prevent recapturing changes on a transaction-by-transaction basis. The point-in-time recovery with the bulk-logged recovery model is not possible because none of the minimally logged operations can be restored.

Let’s consider another scenario:

This picture shows that the failure occurs at 15:00, but before this a minimally logged operation took place and the next transaction log backup (marked yellow, at 16:00) contains bulk-logged changes. This means that point-in-time recovery is possible only up to the previous log backup. If you try to restore your database to later point-in-time you’ll get the following errors:

This log backup contains bulk-logged changes. It cannot be used to stop at an arbitrary point in time.

The STOPAT clause specifies a point too early to allow this backup set to be restored. Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point.

RESTORE LOG is terminating abnormally

So the best sequence of actions in this case is to restore the database to its state as of 14:00. All other changes will be irretrievably lost.

Here’s how to do it:

RESTORE DATABASE your_database FROM DISK = 'full_10_00.bak' WITH NORECOVERY, REPLACE
RESTORE LOG your_database FROM DISK = 'log_12_00.bak' WITH NORECOVERY
RESTORE LOG your_database FROM DISK = 'log_14_00.bak' WITH RECOVERY

It is also important to mention that under the bulk-logged recovery model if a log backup covers any bulk operations it contains both log records and the data pages that were changed by bulk operations.  Also, if read/write database is changed to read-only access after a bulk-logged operation, subsequent log backups might capture more data than necessary. This is because the data file cannot be updated to track which data extents were changed by a bulk-logged operation.

Use the following command to set bulk-logged recovery model :

ALTER DATABASE your_database SET RECOVERY BULK_LOGGED