101
0/252
Loading content...
Consider a 10-terabyte enterprise data warehouse that processes 500,000 transactions daily. A full backup of this system takes 8 hours to complete and requires 10 TB of storage. If the organization needs hourly backup protection, running 24 full backups per day would consume:
Yet this same organization sees only about 50 GB of actual data changes per hour—a mere 0.5% of the total database. Why capture 10 TB when only 50 GB changed?
This fundamental insight drives incremental backups: the strategy of capturing only what changed since the last backup, dramatically reducing backup time, storage, and resource consumption while maintaining comprehensive protection.
By the end of this page, you will understand the mechanics of incremental backups, how databases track changes between backups, the distinction between cumulative and differential incrementals, the mathematics of backup chain restoration, and the critical tradeoffs that determine when incremental backups are the optimal choice.
An incremental backup captures only the data that has changed since the most recent backup of any type (full or incremental). Each incremental backup is a delta—the difference between the current database state and the state captured in the previous backup.
Formal Definition:
Let D(t) represent the database state at time t. Given:
Each incremental backup captures:
Bᵢ = D(tᵢ) - D(tᵢ₋₁) = Δ(tᵢ₋₁, tᵢ)
Where Δ represents all changes (inserts, updates, deletes) that occurred between tᵢ₋₁ and tᵢ.
To restore the database to state D(tₙ), you must apply the full backup plus all incrementals:
D(tₙ) = B₀ + B₁ + B₂ + ... + Bₙ
Think of a full backup as a complete printed copy of a book, while each incremental backup is like a journal of edits made since the last update. To get the current version of the book, you need the original printed copy plus every journal of edits applied in order. Miss one journal, and you have gaps in your edits.
| Characteristic | Full Backup | Incremental Backup |
|---|---|---|
| Data Captured | Entire database | Only changes since last backup |
| Backup Size | Size of database | Size of changes (typically 0.1-5%) |
| Backup Duration | Hours for large DBs | Minutes typically |
| Storage Required | Full size per backup | Fraction per backup |
| Restoration Speed | Fast (single file) | Slower (chain of files) |
| Dependency | Self-contained | Requires full + all prior incrementals |
| Risk Exposure | None | Chain corruption risk |
For incremental backups to work, the database system must know what changed since the last backup. Different database systems employ various change tracking mechanisms:
Core Tracking Approaches:
Block Change Tracking (BCT) maintains a bitmap or log that records which database blocks (pages) have been modified since the last backup.
How It Works:
Example: Oracle Block Change Tracking
-- Enable block change tracking
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/u01/oradata/change_tracking.ctf';
-- View tracking status
SELECT * FROM V$BLOCK_CHANGE_TRACKING;
Advantages:
Considerations:
Enterprise backup systems often implement multi-level incremental backups to balance backup efficiency with restoration complexity. This creates a hierarchy where backups at different levels capture changes relative to different reference points.
Level-Based Incremental System:
This hierarchy allows flexible backup strategies that optimize for various recovery scenarios.
Cumulative vs. Differential Incrementals:
This distinction is critical and often confused:
Differential Incremental (True Incremental):
Cumulative Incremental (Differential Backup):
Note: The terminology varies by vendor. Oracle calls cumulative backups 'differential level 1', while SQL Server uses 'differential' for cumulative and 'log backup' for true incrementals.
| Day | Daily Changes | Differential Inc. Size | Cumulative Inc. Size |
|---|---|---|---|
| Sunday (Full) | N/A | 1000 GB (full) | 1000 GB (full) |
| Monday | 10 GB | 10 GB | 10 GB |
| Tuesday | 8 GB | 8 GB | 18 GB |
| Wednesday | 12 GB | 12 GB | 30 GB |
| Thursday | 7 GB | 7 GB | 37 GB |
| Friday | 15 GB | 15 GB | 52 GB |
| Saturday | 9 GB | 9 GB | 61 GB |
| Weekly Storage | 61 GB changes | 1061 GB total | 1208 GB total |
Differential incrementals use less storage but require more files for recovery. Cumulative incrementals use more storage but require fewer files for recovery. The choice depends on whether you're more constrained by storage or recovery time.
Incremental backups create backup chains—sequences of dependent backup files that must all be present and valid for restoration. Understanding chain dynamics is critical for reliable recovery.
Anatomy of a Backup Chain:
Chain Dependency Rules:
Recovery Time Calculation:
For a chain with full backup F and n incrementals:
Recovery Time = Time(restore F) + Σᵢ₌₁ⁿ Time(apply Iᵢ)
Each incremental adds to recovery time. Long chains mean slow recovery.
If Incremental 3 in the diagram above is corrupted or lost, you can only restore up to the end of Incremental 2. All changes in Incrementals 3, 4, and 5 are unrecoverable—even though those backups may be perfectly intact. This is why the full backup frequency determines your maximum data loss in chain-break scenarios.
Let's examine how major database systems implement incremental backups, with practical commands and configurations.
123456789101112131415161718192021222324252627282930313233343536
-- Oracle RMAN Incremental Backup Strategy -- First, enable block change tracking for efficiencyALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '+DATA/orcl/bct/change_tracking.ctf'; -- Level 0 (Full backup) - Run weeklyRMAN> BACKUP INCREMENTAL LEVEL 0 AS COMPRESSED BACKUPSET DATABASE TAG 'WEEKLY_LEVEL0' PLUS ARCHIVELOG; -- Level 1 Differential - Changes since last Level 0 OR Level 1-- Run dailyRMAN> BACKUP INCREMENTAL LEVEL 1 AS COMPRESSED BACKUPSET DATABASE TAG 'DAILY_LEVEL1' PLUS ARCHIVELOG; -- Level 1 Cumulative - Changes since last Level 0 only-- Alternative to differentialRMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE AS COMPRESSED BACKUPSET DATABASE TAG 'DAILY_CUMULATIVE'; -- Merged Incremental Strategy (Incrementally Updated Backup)-- Creates image copies that stay currentRMAN> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'daily_copy' DATABASE; RMAN> RECOVER COPY OF DATABASE WITH TAG 'daily_copy';Oracle's incrementally updated backup strategy maintains image copies that are 'rolled forward' with each incremental. This gives you the restore speed of a full backup with the backup efficiency of incrementals.
Understanding the quantitative advantages of incremental backups helps in designing optimal backup strategies. Let's analyze the numbers.
Scenario: 1 TB Production Database
| Strategy | Calculation | Total Storage | Total Backup Time |
|---|---|---|---|
| Daily Full Backups | 30 × 1 TB | 30 TB | 120 hours (4h × 30) |
| Weekly Full + Daily Inc. | (4 × 1 TB) + (30 × 20 GB) | 4.6 TB | 16h + 5h = 21 hours |
| Weekly Full + Daily Cum. | (4 × 1 TB) + cumulative growth | ~7 TB | 16h + 10h = 26 hours |
Storage Savings:
Incremental Strategy Savings = (30 TB - 4.6 TB) / 30 TB = 84.7% reduction
Time Savings:
Backup Time Savings = (120h - 21h) / 120h = 82.5% reduction
The Compound Effect:
As databases grow, these savings compound. For a 50 TB data warehouse, daily full backups become practically impossible (200+ hours monthly), while incremental strategies remain manageable.
Incremental backup efficiency is directly proportional to the change rate. A database with 0.5% daily changes sees 97.5% storage reduction versus full backups. A database with 10% daily changes sees only 75% reduction. Know your change rate to accurately project backup resource needs.
The efficiency gains of incremental backups come with a fundamental tradeoff: recovery complexity increases. Understanding this tradeoff is essential for meeting Recovery Time Objectives (RTO).
Recovery Time Formula:
For a system where:
Total Recovery Time = T_full + (n × T_inc)
Example:
Compare to restoring yesterday's full backup: 2 hours. The incremental strategy adds 75% to recovery time in this scenario.
Organizations must balance backup efficiency against recovery requirements. If your RTO is 1 hour but recovery from incrementals takes 3 hours, your backup strategy doesn't meet business requirements—regardless of how much storage it saves. Always design backup strategies starting from recovery requirements, not backup convenience.
Incremental backups are essential for practical, resource-efficient database protection. Let's consolidate the key concepts:
What's Next:
We've now covered full backups (complete captures) and incremental backups (change-only captures). The next page explores Differential Backups—the middle ground that captures all changes since the last full backup, offering a balance between the extremes.
You now understand incremental backups: how they work, why they're efficient, how to implement them across different database systems, and the tradeoffs they involve. This knowledge prepares you to design hybrid backup strategies that leverage both full and incremental approaches.