SQL differential backup and restore

A differential backup creates a copy of all the pages in a database modified after the last database backup. Differential logs are used primarily in heavily used systems where a failed database must be brought back online quickly. Differential backups are smaller than full database backups; therefore, they have less of an effect on the system while they run.

For example, a site executes a full database backup on Sunday night. A set of transaction log backups is made every four hours during the day, with the backups from one day overwriting the backups from the day before. Each night the site makes a differential backup. If one of the data disks for the database fails at 9:12 A.M. on Thursday, the site can:

1. Back up the current transaction log.
2. Restore the database backup from Sunday night.
3. Restore the differential backup from Wednesday night to roll the database forward to that point.
4. Restore the transaction log backups from 4:00 A.M. and 8:00 A.M. to roll the database forward to 8:00 A.M.
5. Restore the log backup taken after the failure. This will roll the database forward to the time of the failure.

Consider using differential database backups when:

• Only a relatively small portion of the data in the database has changed since the last database backup. Differential database backups are particularly effective if the same data is modified many times.
• You are using the Simple Recovery model and want more frequent backups, but don't want to do frequent full database backups.
• You are using the Full or Bulk-Logged Recovery model and want to minimize the time it takes to roll forward transaction log backups when restoring a database.

A recommended process for implementing differential database backups is:

1. Create regular database backups.
2. Create a differential database backup periodically between database backups, such as every four hours or more for highly active systems.
3. If using Full or Bulk-Logged Recovery, create transaction log backups more frequently than differential database backups, such as every 30 minutes.

The sequence for restoring differential database backups is:

1. Restore the most recent database backup.
2. Restore the last differential database backup.
3. Apply all transaction log backups created after the last differential database backup was created if you use Full or Bulk-Logged Recovery.

Reducing Recovery Time

Using database, differential database, and transaction log backups together can reduce the amount of time it takes to restore a database back to any point in time after the database backup was created. Additionally, creating both differential database and transaction log backups can increase the robustness of a backup in the event that either a transaction log backup or differential database backup becomes unavailable, for example, due to media failure.

Typical backup procedures using database, differential database, and transaction log backups create database backups at longer intervals, differential database backups at medium intervals, and transaction log backups at shorter intervals. For example, create database backups weekly, differential database backups one or more times per day, and transaction log backups every ten minutes.

If a database needs to be recovered to the point of failure, for example, due to a system failure:

1. Back up the currently active transaction log. This operation will fail if the transaction log has been damaged.
2. Restore the last database backup created.
3. Restore the last differential backup created since the database backup was created.
4. Apply all transaction log backups, in sequence, created after the last differential backup was created, finishing with the transaction log backup created in Step 1.

Note  If the active transaction log cannot be backed up, it is possible to restore the database only to the point when the last transaction log backup was created. Changes made to the database since the last transaction log backup are lost and must be redone manually.

By using differential database and transaction log backups together to restore a database to the point of failure, the time taken to restore a database is reduced because only the transaction log backups created since the last differential database backup was created need to be applied. If a differential database backup was not created, then all the transaction log backups created since the database was backed up need to be applied.

For example, a mission-critical database system requires that a database backup is created each night at midnight, a differential database backup is created on the hour, Monday through Saturday, and transaction log backups are created every 10 minutes throughout the day. If the database needs to be restored to its state at 5:19 A.M. on Wednesday:

1. Restore the database backup created on Tuesday night.
2. Restore the differential database backup created at 5:00 A.M. on Wednesday.
3. Apply the transaction log backup created at 5:10 A.M. on Wednesday.
4. Apply the transaction log backup created at 5:20 A.M. on Wednesday, specifying that the recovery process only applies transactions that occurred before 5:19 A.M.

Alternatively, if the database needs to be restored to its state at 3:04 A.M. on Thursday, but the differential database backup created at 3:00 A.M. on Thursday is unavailable:

1. Restore the database backup created on Wednesday night.
2. Restore the differential database backup created at 2:00 A.M. on Thursday.
3. Apply all the transaction log backups created from 2:10 A.M. to 3:00 A.M. on Thursday.
4. Apply the transaction log backup created at 3:10 A.M. on Thursday, specifying that the recovery process only applies transactions that occurred before 3:04 A.M.