Skip to content

Will a SQL Server differential still restore against the old full backup after an extra backup?

Read this article in ไทย 中文

Will a SQL Server differential still restore against the old full backup after an extra backup?

An extra file can change the next backup’s base

An ERP database has a conventional full database backup on Sundays and nightly differentials. Before making an extra full copy for a midweek test, decide whether it should become the new base. In SQL Server a conventional full database backup establishes a new base for subsequent differentials. A copy-only full can restore a complete database independently while preserving the regular differential base.

Trace the actual sequence

Suppose Sunday produces A, Tuesday’s differential is B, Wednesday morning’s conventional full is C and Wednesday night’s differential is D. To recover to Wednesday night, restore C and then D. B still belongs with A. Pairing A with D is wrong even if both files are in the regular backup folder and D is the newest file there.

Change only one assumption. If C is copy-only and no other conventional full intervenes, D still uses A. Restore A and then D without applying B: differentials accumulate changes since their base, rather than just changes since the previous night. C can restore the database to the state captured in C, but it cannot serve as D’s base.

Verify backup-set metadata

Have the administrator check the database, backup type, start and finish times, is_copy_only flag and differential-base identifiers in backup history or backup-set headers. Match them to media that is actually readable. Filenames and file-copy timestamps are insufficient. If conventional full C was stored only on a test machine and deleted, flag that D lacks its base for this restore path. Identify other recoverable sets and their time gap before revising retention.

Prove the pair on an isolated system

On an authorised isolated target, restore the identified pair and validate the database and a business record, such as the latest goods receipt expected at D. Record the sets used, the recovered time and the user’s check. This example covers whole-database full and differential backups. For a time between those snapshots, assess the recovery model and required transaction log sequence separately.

A main database, its linked backup and a separate copy

Sources

Back to all articles

Start the conversation

Tell us briefly what concerns you and our team will get back to you. What you send stays confidential.