Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

Differential backup getting bigger every day: why the base never moves

Technical article
Backup & Recovery

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

A differential backup records everything that has changed since its base, which is the last full backup. Each day's differential therefore includes the previous day's changes plus the new ones, so it grows until a new full backup replaces the base. Growth within a cycle is normal. Growth that never resets means the base is not being renewed, or the backup you call a full does not count as a base.

Check three things in order: when the last full backup that counts as a base was taken, whether it was a copy-only backup, and whether some other tool or job takes the fulls.

What the symptom means

Microsoft describes the mechanism for SQL Server: "A differential backup is based on the most recent, previous full data backup. A differential backup captures only the data that has changed since that full backup." It captures "the state of any extents (collections of eight physically contiguous pages) that have changed between when the differential base was created and when the differential backup is created", so "the size of a given differential backup depends on the amount of data that has changed since the base. Generally, the older a base is, the larger a new differential backup will be."

Microsoft also says that differentials "can eventually approach the differential base in size", and recommends a new full backup "at set intervals to establish a new differential base". Microsoft's own example is a weekly full with differentials between.

Bareos works the same way. Its documentation says a Differential level backs up all files "that have changed since the last successful Full backup of the same Job". If the Director finds no valid Full for the same job, FileSet and client, the Differential "will be upgraded into a Full backup". The Director's Max Full Interval directive sets the maximum age of the most recent successful Full that Incremental and Differential jobs may rely on. Past that age they are upgraded to Full.

Bareos adds one more rule. Max Diff Interval sets the maximum age of the most recent successful Differential needed to run Incrementals. When the last Differential is older than the interval, Incrementals are upgraded to Differentials. That keeps the restore chain short, but it also means a backup you scheduled as an Incremental can arrive larger, as a Differential.

Likely causes

Cause What you see How to confirm
No full backup runs, or it fails Differentials grow for weeks The date of the last full that finished successfully
The full is copy-only (SQL Server) Fulls appear in the list, but sizes keep climbing Microsoft: a copy-only backup leaves the differential bitmap alone, so it is not a base; check is_copy_only
Related check, a different symptom: a second tool takes fulls It does not make differentials grow. A new base makes the next differential smaller, and restores can fail because the differential no longer matches the full you hold Microsoft: any non-copy-only full can become the base; compare full backup history across tools
Bareos Max Full Interval too long or absent No automatic Full when a scheduled one is missed Directive value against the date of the last Full
A scanner or tool changes file attributes (Bareos, file backups) Many unchanged files re-saved each night The Bareos documentation: a change of st_ctime makes a file back up again
The workload rewrites large parts of the data Fast growth from the first day Daily differential sizes, compared with the daily change rate you expect

Prove the cause before changing anything

For SQL Server, msdb.dbo.backupset holds one row per backup set. Microsoft documents the columns database_name, type (D is a database full, I a differential), is_copy_only, backup_start_date, backup_size and differential_base_lsn. This query only reads:

SELECT database_name, type, is_copy_only, backup_start_date,
       backup_size, differential_base_lsn
FROM msdb.dbo.backupset
WHERE database_name = N'<database>' AND type IN ('D','I')
ORDER BY backup_start_date DESC;

Read the result this way:

  • Rows with type = 'D' and is_copy_only = 0 appear at your full interval, and backup_size of the I rows drops after each one. The base is moving.
  • Fulls are is_copy_only = 1, or there is no recent D row. The base is old, or fixed.
  • The differential_base_lsn value of I rows stays the same within a cycle. Microsoft defines it as the base LSN of the differential; reading that definition, a new value means a new base. The same value across weeks means the base never moved. Microsoft says the value is NULL for a multibased differential (file or filegroup backups) and for non-differential rows, so this check applies to single-based database differentials only.
  • D rows come from a different login or job than you expect. Another tool is taking fulls.

For Bareos, use bconsole. list job=<job-name> lists all jobs with that name, and list jobs lists all of them. Find the most recent Full, then count the days to the newest Differential, and look at how large each Differential is. A run of Differentials after a single old Full is the same pattern.

Fix

Fix the base, not the size.

Missing or failing full. Find why the full does not run or does not finish, and fix that first. Then take a full that counts as a base with your backup tool. A full takes a lot of time and space, so choose the window deliberately.

Copy-only fulls. Microsoft: "The differential bitmap isn't updated by a copy-only backup. Therefore, a copy-only backup doesn't affect subsequent differential backups." Use copy-only for ad hoc backups that must not disturb the chain, and a normal full for the scheduled one. Decide which one job owns the scheduled full, so the base is always the backup you expect.

Disruptive step: taking a normal full to reset the base changes which backup the next differentials are based on. Any differential taken before it can no longer be applied on top of the new full. Keep the old full and its differentials until the new set has been restored successfully once. There is no way to undo a base change; you can only restore from the older set while it remains.

A second tool. Decide which tool owns the full backups, and set every other tool to copy-only. Two tools both taking normal fulls shifts the base unpredictably, and a differential restore needs the right base.

Bareos. Set Max Full Interval to slightly longer than the gap between your scheduled Fulls, so a missed Full is replaced automatically at the next run rather than weeks later. Remember that it also makes Differentials upgrade to Full once the last Full is older than the interval.

Risk: changing Max Full Interval adds extra Fulls (each reads the whole data set) when the last Full is older than the value, and a very long value delays the automatic Full. To back out, restore the previous value or remove the directive; Fulls already taken only add restore points and are not undone.

If scanners touch file attributes, follow the Bareos documentation: for Sophos, the --no-reset-atime option avoids the reset of access time that changes st_ctime.

Confirm the fix

After the next full completes, the next differential should be small again, close to one day's change. In SQL Server, the new I rows should show a new differential_base_lsn. In Bareos, list job=<job-name> should show a recent Full followed by small Differentials. If the first differential after the full is still large, the workload itself rewrites a lot, which a different schedule cannot fix.

Example (illustrative): a 400 GiB database on sql01 takes a full on Sunday. Assume Monday's differential is 6 GiB, and each later day adds about 4 GiB of extents not yet captured, because most changes hit extents already in the differential. That assumption is why growth is linear here; real growth slows as the differential nears the size of the database. Sizes are Monday 6, Tuesday 10, Wednesday 14, Thursday 18, Friday 22 and Saturday 26 GiB, which totals 96 GiB for the week. If no new full runs for 30 days, day 30 is 6 + 4 x 29 = 122 GiB, about 30.5 percent of the 400 GiB base. At a line rate of 125 MB/s (1 Gbit/s line rate, so a real transfer takes longer), 26 GiB takes about 3.7 minutes and 122 GiB about 17.5 minutes. A daily incremental of 4 GiB takes about 34 seconds. Restoring still needs the base plus the latest differential, two files, but the nightly cost of the differential has become 30 times that of the incremental (122 divided by 4 is 30.5).

What to watch afterwards

  • Plot the size of each differential against days since the last full. A sawtooth that resets is healthy.
  • Alert when no full has succeeded for longer than your interval.
  • Keep a short note of which tool owns the full backup of each database.
  • After a read-only database is rebuilt, restored, or detached and attached, take a new full: Microsoft says the differential-base information is lost in those cases.

Risks and limits

These commands have not been run or lab-tested. The query reads a system table on SQL Server; column names are as documented for SQL Server and Azure SQL Managed Instance, and other engines differ. The example uses assumed growth, not a measured system.

A differential is not a cheaper way to avoid fulls. It needs its base to be intact: if the base full is lost or damaged, every differential from it is unusable. Do not delete an old full while differentials still rely on it.

Sources