Microsoft SQL Server Backup Models
master - simple - model - full (default) msdb - simple (default) resource - none tempdb - simple
Reference: http://msdn.microsoft.com/en-us/library/ms175987(SQL.90).aspx
When to Use the Simple Recovery Model
Use the simple recovery model if the following are all true:
- Point of failure recovery is unnecessary. If the database is lost or damaged, you are willing to lose all the updates between a failure and the previous backup.
- You are willing to risk losing some data in the log.
- You do not want to back up and restore the transaction log, preferring to rely exclusively on full and differential backups.
When to Use the Full Recovery Model
Use the full recovery model and, optionally, also the bulk-logged recovery model if any one of the following is true:
- You must be able to recover all the data.
- If the database contains multiple filegroups, and you want piecemeal restore of read/write secondary filegroups and, optionally, read-only filegroups.
- You must be able to recover to the point of failure.
- You want to be able to restore individual pages.
- You are willing to incur the administrative costs of transaction log backups.
When to Use the Bulk-Logged Recovery Model
The bulk-logged recovery model is intended strictly as an adjunct to the full recovery model. We recommend that you use it only during periods in which you are running large-scale bulk operations, and in which you do not require point-in-time recovery of the database.
- Is the database subject to periodic bulk operations on the database?
Under this recovery model, most bulk operations are only minimally logged. If you use the full recovery model, you can switch temporarily to the bulk-logged recovery model before you perform such bulk operations. For information about what operations are bulk logged under the bulk-logged recovery model, see Minimally Logged Operations.
Generally, the bulk-logged recovery model resembles the full recovery model, except that it minimally logs most bulk operations. A transaction log backup captures the log and, also, the results of any minimally logged operations that have completed since the last backup. This can make the log backups very large. Therefore, the bulk-logged recovery model is intended only for use during bulk operations that allow for minimal logging. We recommend that you use the full recovery model the rest of the time. As soon as a set of bulk operations finishes, we recommend that you immediately switch back to the full recovery model.
