Full, Incremental, and Differential Backups

In this lesson, you will learn how to design a tiered backup strategy that balances the trade-off between storage overhead and Mean Time to Recovery (MTTR).

The Tiered Backup Strategy

When dealing with large-scale databases, taking a FULL backup daily is often prohibitively expensive in terms of I/O and storage. A tiered strategy uses a mix of FULL, DIFFERENTIAL, and INCREMENTAL backups to optimize the recovery chain.

• Full Backups capture the entire state of the database. They are your baseline, but they are slow and storage-heavy.
• Differential Backups capture all data changed since the last FULL backup. As the week progresses, these grow in size, but they are faster to restore than a chain of individual increments.
• Incremental Backups capture only the data changed since the last backup of any type. These are extremely fast to create and consume minimal storage, but they require a longer restoration sequence.

Designing the Hierarchy

A classic high-performance strategy follows a weekly cycle:

1. Sunday: Perform a FULL backup.
2. Mon–Sat: Perform daily INCREMENTAL backups to minimize daily impact.
3. Ad-hoc: Use DIFFERENTIAL backups at mid-week intervals (e.g., Wednesday) to "reset" the recovery chain, ensuring you never have to replay more than a few days of incremental logs during a restore.

Restoration Logic

The restoration speed depends on the length of your recovery chain. If you lose your data on Friday, the sequence is: Restore FULL (Sunday) + Restore DIFFERENTIAL (Wednesday) + Restore INCREMENTAL (Thursday) + Restore INCREMENTAL (Friday).

-- Example Strategy
-- Weekly: Full Backup
BACKUP DATABASE Production TO DISK = 'weekly_full.bak';

-- Daily: Incremental (Changes since yesterday)
BACKUP DATABASE Production TO DISK = 'daily_inc.bak' WITH INCREMENTAL;

-- Mid-week: Differential (Changes since Sunday)
BACKUP DATABASE Production TO DISK = 'midweek_diff.bak' WITH DIFFERENTIAL;

By keeping the chain short through strategic DIFFERENTIAL checkpoints, you limit the number of files the database engine must merge, significantly improving your recovery speed without the cost of daily FULL backups.

Key Takeaway: Optimize your backup strategy by using frequent incremental backups to minimize storage costs and periodic differential backups to cap the maximum number of files required for a rapid restore.