Diff backups – under the covers | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Diff backups – under the covers

I use SQL Server 2000 Differential backups and understand that a diff backup represents the changes in a database from the point of the last full backup. However, I would like to know how SQL Server actually goes about understanding what those differences are? I don’t think it’s a case of simply looking at the transaction log because transaction log truncations can occur between the time of the last full backup and the most recent differential backup. So, how does it work under the covers? I know this is a case of want to know rather than need to know but it would interesting to find out. Clive
From BOL: 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: Create regular database backups.
Create a differential database backup periodically between database backups, such as every four hours or more for highly active systems.
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: Restore the most recent database backup.
Restore the last differential database backup.
Apply all transaction log backups created after the last differential database backup was created if you use Full or Bulk-Logged Recovery.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell