Instruction 05-database-backup-and-restore: Reading

A database backup is useful only when it can be restored into a valid and trusted state. Simply copying a database file while an application is writing to it may capture a mixture of old and new pages, omit related transaction-log files, or represent an undefined point in time. Database-aware tools avoid this problem by coordinating with the engine. In this lesson, Python calls SQLite's online backup API, which copies a consistent database snapshot while the source database remains open.

Backups are commonly described as physical or logical. A physical backup preserves engine-native storage structures and is often efficient for full-system recovery, but it may depend on a compatible database engine and version. A logical backup exports schemas and records into a portable representation such as SQL or a structured data format. Logical backups can be useful for selective recovery and migrations, but restoring a large export may require recreating indexes, constraints, and other objects. Neither form is universally superior; the format must match the recovery objective.

RPO and RTO turn vague expectations into engineering requirements. If backups are taken every 24 hours and no transaction logs are retained, a failure just before the next backup could lose almost 24 hours of changes. That design therefore cannot satisfy a one-hour RPO. RTO considers the complete recovery workflow: locating credentials, retrieving artifacts, provisioning capacity, restoring data, applying logs, validating the result, and reconnecting applications. A backup job completing successfully says little about the time required for those later steps.

A trustworthy workflow separates backup creation from backup verification. First, create a database-aware backup. Second, calculate and store an integrity digest with identifying metadata such as creation time, source name, tool, and format. Third, restore to an isolated destination. Fourth, run structural checks and application-level assertions. Structural checks can discover damaged database pages, while application-level checks confirm facts that matter to the service, such as the presence of expected records and valid relationships. Finally, record the outcome so operators can demonstrate when recovery was last tested.

A checksum is not a substitute for a restore test. A backup can retain the same hash while still being unusable because it was empty, encrypted with a lost key, created by an incompatible tool, or missing required transaction logs. Conversely, a changed checksum does not explain why the artifact changed; it only signals that investigation is required. Hash metadata must itself be protected because an attacker able to replace both the backup and its recorded digest can conceal tampering.

Restores should normally begin in an isolated location. Replacing a live database before examining the recovered copy can turn one incident into a larger outage. An isolated restore lets the operator check engine compatibility, integrity, row counts, constraints, timestamps, and critical business records. The operator can then decide whether to promote the recovered database, replay newer transactions, merge selected records, or reject the backup.

Backup policy must also account for correlated failure. A backup stored on the same disk and host as the source database may help with a mistaken edit, but it does not protect against disk loss, theft, ransomware, or destruction of the host. Production designs should use separate failure domains, restricted credentials, encryption, retention controls, and at least one copy that cannot be altered through normal production credentials. This lab intentionally keeps all files inside its classroom directory, so it demonstrates workflow rather than geographic or administrative separation.