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.

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.

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.

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	Changed content: Moved the introduction to backup types to Backup Overview (SQL Server). Inserted the "Minimizing Work-Loss Exposure" header above the discussion of work-loss exposure.
5 December 2005	Changed content: Incorporated the former "Overview of Simple Recovery" topic. Added new figures.