551
Chapter 21: Backup and Recovery Planning
21
FIGURE 21-1
A typical recovery plan using the simple recovery model includes only full and differential
backups.Simple Recovery Model
Sample Backup PlanSundayFullMondayDifferentialTuesdayDifferentialWednesdayDifferentialThursdayDifferentialFridayDifferentialSaturdayDifferentialWhen restoring from a simple recovery plan:- Restore the most recent full backup.
- Restore the most recent (optional) single differential backup.
Best Practice
Simple recovery is mostly used for test and development databases or databases containing mostly
read-only data. Simple mode can, and often is, used for databases which you don’t necessarily want or
need to log large transac tions such as a data warehouse. Simple recover y should not be used where loss
of data since the last full or differential backup is unacceptable. In these cases, full recovery model is
recommended. Full recovery model is also a requirement for Database Mirroring and Log Shipping. The
bottom line is: Know your data, and plan your recovery plan accordingly with what the business can accept.The Full Recovery Model
The full recovery model offers the most robust recovery plan. Under this model all trans-
actions, including bulk-logged operations, are fully logged in the transaction log. Even
system functions such as index creation are fully logged. The primary benefi t of this model
is that every committed transaction in the database can be restored right up to the point
when failure occurred.Best Practice
Use full recovery model for production user databases where data loss since last full or differential
backup is unacceptable. Although it can run on a single drive system, the transaction log should be
located on a fault-tolerant disk subsystem, physically separate from the data fi les, to ensure a high
level of transactional durability.c21.indd 551c21.indd 551 7/31/2012 9:22:46 AM7/31/2012 9:22:46 AM
http://www.it-ebooks.info