Full Recovery
The Full Recovery model uses database backups and transaction log backups to provide complete protection against media failure. If one or more data files is damaged, media recovery can restore all committed transactions. In-process transactions are rolled back.
Full Recovery provides the ability to recover the database to the point of failure or to a specific point in time. To guarantee this degree of recoverability, all operations, including bulk operations such as SELECT INTO, CREATE INDEX, and bulk loading data, are fully logged.
The backup strategy for full recovery consists of:
- Database backups.
- Differential backups (optional).
- Transaction log backups.
- Full and bulk-logged recovery are similar and many users of the Full Recovery model will use the Bulk-Logged model on occasion. For more information, see Bulk-Logged Recovery.
Recovering in the Event of Media Failure
You can restore a database to the state it was in at the point of failure if the current transaction log file for the database is available and undamaged. To restore the database to the point of failure:
- Back up the currently active transaction log. For more information, see Transaction Log Backups.
- Restore the most recent database backup without recovering the database.
- If differential backups exist, restore the most recent one.
- Restore each transaction log backup created since the database or differential backup in the same sequence in which they were created without recovering the database.
- Apply the most recent log backup (created in Step 1), and recover the database.
Important To protect against loss of transactions under the Full Recovery model, the transaction log must be protected against damage. It is strongly recommended that fault-tolerant disk storage be used for the transaction log.
To create a database backup