Creating and Restoring Differential Database Backups

You can supplement database backups by using differential database backups that record only the changes made to the database after the last database backup. For example, you can create a database backup nightly, and create differential database backups at shorter intervals, such as every four hours.

Each differential database backup records all modifications made to a database after the last database backup, not just the changes made after the last differential database backup. If you create a database backup, followed by multiple differential database backups, you do not have to restore the database backup followed by all of the differential database backups; you only have to restore the database backup and the last differential database backup created.

For example, assume you have the following database and differential database backups:

The differential database backup created at 6:00 P.M. on Wednesday contains all changes made to the database after the database was backed up at midnight on Tuesday. The differential database backup created at noon on Thursday contains all changes made to the database after the database was backed up at midnight on Wednesday. To restore the database to its state at noon on Thursday, perform these steps in order:

1. Restore the database backup created at midnight on Wednesday.
2. Restore the differential database backup created at noon on Thursday.

Any changes made after noon on Thursday are lost unless transaction log backups are also used to restore the database.

In this example, if the database backup from midnight on Wednesday is lost or damaged, it is possible to restore the database only to its state at 6:00 P.M. on Wednesday. This is because the differential database backups created on Thursday apply only to the previous database backup (in this example, the lost database backup created at midnight on Wednesday).

Note Because transaction log backups cannot be created after nonlogged operations occur in a database, differential database backups can be created instead of creating a full database backup.

Restoring a differential database backup is similar to restoring a database backup; it leaves the database in the same state it was in when the differential database backup completed, although incomplete transactions will have been rolled back.

Differential database backups are often supplemented by creating multiple transaction log backups after each differential database backup is created. By using a combination of database, differential database, and transaction log backups, recovery time and the amount of potential data loss due to failure can be minimized. For more information, see Using Differential Database Backups with Transaction Log Backups.

Backup Restrictions

Using Microsoft® SQL Server™, backup operations can occur while the database is online and in use. However, some operations are not allowed during a differential database backup operation:

• Creating or deleting database files
• Creating indexes
• Performing nonlogged operations
• Shrinking either the database (automatically or manually) or the database files. For more information, see Shrinking the Database.

If a backup operation is started when one of these operations is in progress, the backup operation terminates. If a backup operation is already in progress and one of these operations is attempted, the operation fails and the backup operation continues.

To create a database backup

To create a differential database backup

To restore a database backup

To restore a differential database backup

See Also

Using Differential Database Backups with Transaction Log Backups