553
Chapter 21: Backup and Recovery Planning
21
If the disk subsystem containing the transaction log is lost, the database is marked suspect by SQL Server, and it
is not possible to back up the current transaction log. In this case, the best recovery option is to restore to the last
transaction-log backup. Other reasons for a database being marked suspect would be that the database fi le itself
has been removed or renamed.- Restore the most recent full backup.
- Restore the most recent single differential backup, if one has been made since the
last full backup. - Restore, in sequence, all the transaction-log backups made since the time of the
last full or differential backup. If the last backup were a full backup, then restoring
it is suffi cient. If the last backup were a differential backup, you need to restore
the most recent full backup before restoring the most recent differential.
The Management Studio restore form (discussed in the section “Performing the Restore
with Management Studio” later in this chapter) automatically helps you choose the correct
set of backups, so it’s not as complicated as it sounds.
Bulk-Logged Recovery Model
The bulk-logged recovery model is similar to the full recovery model except that the
following operations are minimally logged:■ (^) Bulk import operations (BCP, BULK INSERT,
and INSERT ... SELECT * FROM OPENROWSET (BULK...))
■ (^) SELECT INTO operations
■ WRITETEXT and UPDATETEXT BLOB operations
■ (^) CREATE INDEX (including indexed views)
■ ALTER INDEX REBUILD or DBCC DBREINDEX operations
■ (^) DROP INDEX
Because this recovery model minimally logs these operations, they run fast. The transac-
tion log marks only that the operations took place and tracks the extents (a group of eight
data pages) affected by the bulk-logged operation. When the transaction log is backed up,
the extents are copied to the transaction log in place of the bulk-logged marker.
The trade-off for bulk-logged operation performance is that the bulk-logged operation is
not treated as a transaction. Although the transaction log itself stays small, copying all
affected extents to the transaction-log backup can make the log-backup fi le large.
Because bulk-logged operations are minimally logged, if a failure should occur after
the bulk-logged operation but before the transaction log is backed up, the bulk-logged
c21.indd 553c21.indd 553 7/31/2012 9:22:47 AM7/31/2012 9:22:47 AM
http://www.it-ebooks.info