552
Part V: Enterprise Data Management
The trade-off for this high level of transactional integrity is a certain amount of
performance:■ (^) Bulk-logged and select-into operations will be slower. If the database doesn't import
data using these methods, this is a moot point.
■ (^) Depending on the database activities, the transaction log may be huge. You can
control this by performing regularly scheduled transaction log backups. Also, if
copious drive space is available, this too is a moot point.
■ Backing up and restoring the transaction log can take longer than it does with the
other recovery models. However, in a crisis, restoring all the data is likely more
important than quickly restoring partial data.
The full recovery model can use all types of database backups. Figure 21-2 shows a typical
backup schedule.
FIGURE 21-2
A sample recovery plan using the full recovery model, using full, differential, and transaction-
log backups.
12am
Full Differential Full
2am
Log Log Log Log Log Log Log Log Log Log
4am 6am
Full Recovery Model
Sample Backup Plan
8am 10am 12pm 2pm 4pm 6pm 8pm 10pm 12am
A sample full-recovery backup plan typically does a full database backup once or twice a
week and differential backups every day or every other night. The transaction log is backed
up throughout the day, from as little as two times a day to as often as every 15 minutes. Or
you could do a daily full backup, with a differential backup every 6 hours and transaction
log backups every 2 hours in between. The frequency of the transaction log backup is based
around the maximum amount of acceptable data loss. For example, perform a transaction
log backup every 15 minutes if you can afford to lose up to 15 minutes of data. You can mix
and match options that make the most sense for your databases, your environment, and
most importantly your resources (i.e. storage available for backup purposes).
To restore from the full-recovery model, do the following:
- Perform a special kind of transaction log backup called a tail-log backup. This cap-
tures all the log records since the last transaction log backup and places the data-
base in a restoring state.
c21.indd 552c21.indd 552 7/31/2012 9:22:46 AM7/31/2012 9:22:46 AM
http://www.it-ebooks.info