Differential Database Backups

A differential database backup records only the data that has changed since the last database backup. You can make more frequent backups because differential database backups are smaller and faster than database backups. Making frequent backups decreases your risk of losing data.

Note  If you have created any file backups since the last full database backup, those files will be scanned by Microsoft® SQL Server™ 2000 at the beginning of a differential database backup. This may cause some degradation of performance in the differential database backup. For more information, see Using File Backups.

You use differential database backups to restore the database to the point at which the differential database backup was completed. To recover to the exact point of failure, you must use transaction log backups. For more information, see Transaction Log Backups.

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.

To create a differential database backup

Transact-SQL

Enterprise Manager

SQL-DMO

To restore a differential database backup

Transact-SQL

Enterprise Manager

SQL-DMO

See Also

Database Backups

Reducing Recovery Time