569
Chapter 21: Backup and Recovery Planning
21
Recovery Sequences
The two most important concepts about recovering a database are as follows:■ A recovery operation always begins by restoring a full backup and then restores any
additional differential or transactional backups. The restore never copies only yes-
terday’s work. It restores the entire database up to a certain point.■ (^) There’s a difference between restore and recover. A restore copies the data back into
the database and leaves the transactions open. Recovery is the process of handling
the transactions left open in the transaction log. If a database-recovery operation
requires that four fi les be restored, only the last fi le is restored WITH RECOVERY.
Only logins who are members of the sysadmins fi xed server role can restore a database
that doesn’t currently exist. sysadmins and db_owners can restore databases that do
currently exist.
The actual recovery effort depends on the type of damage and the previous recovery plans.
Table 21-2 is a comparative listing of recovery operations.
TABLE 21-2 Recovery Sequences
Recovery Model Damaged Database File Damaged Transaction Log
Simple 1) Restore full backup.
2) Restore latest differential
backup (if needed).
It is likely there are unapplied transactions
lost with the transaction log and the data-
base is inconsistent. It is recommended to fall
back on your backups and use the steps doc-
umented for “damaged database fi le.”
Full or
Bulk-Logged
- Back up current transaction
log with the NO_TRUNCATE
option.* - Restore full backup.
- Restore latest differential
backup (if needed). - Restore all the
transaction-log backups since
the last differential or full
backup. All committed
transactions will be
recovered. - Restore full backup.
- Restore the latest differential backup (if
needed). - Restore all the transaction-log backups
since the last differential or full backup.
Transactions made since the last log backup
will be lost.
*If the database uses the bulk-logged recovery model and a bulk-insert operation occurred since the last
transaction-log backup, the backup will fail. Transactions that occurred after the transaction-log backup are
not recoverable.
c21.indd 569c21.indd 569 7/31/2012 9:22:52 AM7/31/2012 9:22:52 AM
http://www.it-ebooks.info