Database Reference
In-Depth Information
Table 30-1.
Data loss based on the database recovery model
Recovery Model
Description
Data Files Corruption
Log Corruption
SIMPLE
Log Backups are not supported.
The database can be restored to
the point of the last full or
differential backup.
Changes since the last full or differential backup must
be redone.
FULL
All operations are fully recorded
in the transaction log.
No data loss.
Changes since the
last LOG backup
must be redone.
BULK-LOGGED
Bulk-copy operations are
minimally logged. All other
operations are fully logged.
No data loss if bulk-copy
operations did not occur since
the last log backup. Otherwise,
changes since the last LOG
backup must be redone.
In the SIMPLE recovery model, all changes since last full or differential backup must be redone. Therefore, this
model is not the best candidate for databases with volatile data. However, the SIMPLE recovery model is perfectly
acceptable when the data is static, for example in data warehouse and/or reporting systems where the data is
refreshed based on some schedule. You can use the SIMPLE recovery model by performing a full database backup
after each data refresh.
Another possible use-case for the SIMPLE recovery model is a database with data that can be easily and
quickly reconstructed from other sources. In these cases, you may consider using this model to avoid transaction log
maintenance.
■
Databases in the siMpLe recovery model do not support features that rely on transaction log scans, such as
Database Mirroring, alwaysOn availability groups, Log shipping, and others.
Note
The FULL and BULK-LOGGED recovery models log regular (non bulk-copy operations) in the same way and have
the same transaction log maintenance requirements. Even though the BULK-LOGGED recovery model improves the
performance of bulk-copy operations due to minimal logging, it is exposed to data loss in the case of data file corruption.
You should avoid using the BULK-LOGGED recovery model because of this. Nevertheless, you may consider switching
the database from the FULL to the BULK-LOGGED recovery model for the duration of bulk-copy operations (for example
during index rebuild) and then switching the database back to the FULL recovery model afterwards.
■
Important
you should perform a full or log backup immediately after you switch the database back to the FuLL
recovery model.
Neither of the recovery models would survive transaction log corruption and keep the database transactionally
consistent. You should store the transaction log on a highly redundant disk array in order to minimize the chance
of such situations. Neither solution, however, is 100 percent redundant. You should make regular log backups to
minimize possible data loss.
The frequency of log backups helps control possible data loss and indicates how much work
must be redone in the case of transaction log corruption
. For example, if you performed a log backup every hour, you
can only lose up to one hour's work when restoring the last log backup.
