Backup Under the Simple Recovery Model
Important
The simple recovery model is inappropriate for production systems for which loss of recent changes is unacceptable. In these cases, we recommend that you use the full recovery model. For more information, see Backup Under the Full Recovery Model.
The simple recovery model provides the simplest form of backup and restore. Backup is easy to manage because the transaction log is never backed up. However, if there are no log backups, a database can be restored only to the end of the most recent backup of the data. If a failure were to occur, updates that are made after the most recent backup of the data are lost.
Sample Backup Strategy
The following illustration shows the simplest backup-and-restore strategy under the simple recovery model. Five full database backups exist, but only the most recent backup, taken at the time t5 has to be restored. Restoring this backup returns the database to the t5 point in time. All later updates, represented by the t6 box, are lost.
.gif "Restoring a simple-model database")
Note
Under the simple recovery model, the transaction log is automatically truncated to remove any inactive virtual log files. Truncation usually occurs after each checkpoint but can be delayed under some conditions. For more information, see Transaction Log Truncation.
Minimizing Work-Loss Exposure
Under the simple recovery model, work-loss exposure increases over time until the next full or differential backup is taken. Therefore, we recommend that you schedule backups frequently enough to avoid losing lots of data without your backups becoming unmanageable.
The following illustration shows work-loss exposure for a backup plan that uses only database backups. This strategy is appropriate only for a small database that you can back up fairly frequently.
.gif "Shows work-loss exposure between database backups")
The following illustration shows a backup strategy that reduces work-loss exposure by supplementing database backups with differential database backups. After the first database backup, a series of three differential backups is taken. The third differential backup is large enough that the next backup is a database backup. This establishes a new differential base.
.gif "Full and differential database backups")
Using Backups to Restore a Database
Full and differential backups contain just enough log data to let you recover the database. Restoring a database requires a sequence of restore operations (a restore sequence). A restore sequence starts with restoring a full backup, optionally followed by a corresponding differential backup. In some cases, for example, when restoring files, multiple pairs of full and differential backups may require restoring. After restoring the relevant backups, you must recover the database. For an introduction to restore scenarios, see Overview of Restore and Recovery in SQL Server.
For information about restrictions when restoring backups taken under the simple recovery model, see Restore Restrictions Under the Simple Recovery Model
See Also
Concepts
Copy-Only Backups
Base of a Differential Backup
Backup Devices
Full Database Backups
Introduction to Backup and Restore Strategies in SQL Server
Overview of Restore and Recovery in SQL Server
Considerations for Switching from the Simple Recovery Model
Other Resources
Understanding and Managing Transaction Logs
Using Differential Backups
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
17 July 2006 |
|
5 December 2005 |
|