Recovery Models
You use the SQL Server backup and restore functionality to perform your database backups and restorations. SQL Server provides three recovery models: simple, full, and bulk-logged, and four types of backups: full database, differential, transaction log, and database file or filegroup. SQL Server keeps track of the type and time of backups performed on each table. You can design a backup strategy using all four types of backups on a scheduled basis to ensure that you can restore exactly what you need to in case of a failure.
The following table provides an overview of the benefits and implications of the three recovery models.
| Recovery model | Benefits | Work loss exposure | Point in time recovery |
|---|---|---|---|
| Simple | Permits high-performance bulk copy operations. Reclaims log file space to keep space requirements small. | Changes since the most recent backup must be redone. | Can recover to the end of any backup, and then changes must be redone. |
| Full | No work is lost due to a lost or damaged data file. Can recover to an arbitrary point in time (for example, before an application or user error). | Normally no work is lost. If the log file is damaged, changes since the most recent log file backup must be redone. | Can recover to any point in time. |
| Bulked-Logged | Permits high-performance bulk copy operations. Minimal log file space is used by bulk operations. | If the log file is damaged, or bulk operations occurred since the most recent log file backup, changes since that last backup must be redone. Otherwise, no work is lost. | Can recover to the end of any backup, and then changes must be redone. |
Copyright © 2005 Microsoft Corporation.
All rights reserved.