• Article

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. This recovery model supports both database backups and file backups, which contain both user data and active transaction log data. But separate log backups are not supported. The absence of log backups simplifies managing backup and restore. However, a database can be restored only to the end of the most recent backup.

Sample Backup Strategy

The following illustration shows the simplest backup-and-restore strategy under the simple recovery model. This strategy uses only full database backups, which include all of the data in the database. 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.

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. In contrast to a full backup, a differential backup includes only the changes made since the previous full backup. 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.

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.

Full and differential database backups

For more information about using these types of backups, see Full Database Backups and Using Differential 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 Restore and Recovery Overview (SQL Server).

For information about restrictions when restoring backups taken under the simple recovery model, see Restore Restrictions Under the Simple Recovery Model