Follow the Transaction Logs

About this interactive

A database is records in tables, and on disk it is just files. Every change, a transaction, is first written to a transaction log, and the database applies the changes to its main data file as it goes. Database backups use the log to decide what to save. A full backup copies the whole database. An incremental backup, often called a log backup, copies the transaction log since the last backup and then clears, or truncates, the part it saved. New transactions start a fresh log, so the next incremental holds only what happened since. A differential backup captures every change since the last full and does not clear the log, so each one is bigger than the last until the next full. The lesson describes clearing the log as rolling the logs into the database, a commit. In database terms a commit is when a transaction is made permanent, and the clearing is called truncating the log. Some systems, such as Microsoft Exchange, back up and clear the logs exactly as the lesson draws it; SQL Server builds its differentials from the changed parts of the database file rather than the log. The rule to hold on to is the same everywhere: a differential is everything since the last full; an incremental is everything since the last backup. So restoring follows the same arithmetic as files: the full plus every incremental since it, or the full plus the latest differential.

What you'll learn

Explain how a database's transaction log supports backups: an incremental (log) backup saves the log and then clears it, while a differential leaves it in place
Calculate how many transactions each backup type captures on a schedule, and how many backups a database restore needs

Aligned to

NIST NICE

K0701 Knowledge of data backup and recovery policies and procedures

K0707 Knowledge of database systems and software

K0991 Knowledge of database administration principles and practices

CompTIA Server+

3.7 Given a scenario, implement backup and restore procedures.

NIST 800-53

CP-9 System Backup

Key terms

Transaction: A discrete unit of work or change recorded against a file system or database, used as the basis for journaling and recovery operations.
Full Backup: A complete copy of all selected data, serving as the baseline backup type from which other backup strategies operate.
Differential Backup: A backup type that copies only the data that has changed since the last full backup.
Incremental Backup: A backup type that copies only the data that has changed since the last backup of any type, minimizing storage use but requiring multiple restore steps.
Database Management System: A Database Management System is software that stores, organizes, and provides controlled access to structured data; DBMS security involves access control, encryption at rest, auditing, and injection-attack prevention to protect sensitive records.
Restore: The process of retrieving and applying backed-up data to recover a system or dataset to a previous state.

Topics

Transaction Full Backup Differential Backup Incremental Backup Interactive Fill In

About TechKnowSurge

TechKnowSurge builds IT and cybersecurity professionals through hands-on, concept-first training built around real understanding — not memorization. Free interactive tools, structured programs, and 25+ years of real-world experience, all in one place.

Explore free tools and programs →