Database Backup and Recovery

A database backup is recovery material captured before you need it. Recovery is the controlled process that turns that material into a usable database again.

That distinction matters. A completed backup job proves that a tool wrote something. It does not prove that the files are complete, readable, consistent, or fast enough to restore.

This course gives you a practical map of database backup and recovery. You will learn how objectives shape design, how backup forms differ, and how a restore becomes a verified recovery.

Start with the recovery outcome

Define the business need before choosing a backup tool or schedule.

Recovery point objective (RPO): the point in time to which data must be recovered after an outage.
Recovery time objective (RTO): the maximum time a system resource can remain unavailable before the impact becomes unacceptable.

An RPO of fifteen minutes means your recovery design must preserve a usable point no more than fifteen minutes before the disruption. It does not promise zero data loss.

An RTO of two hours limits the acceptable outage. Backup storage, transfer time, restore speed, validation, and application cutover all consume that time.

Shorter objectives usually cost more. They can require frequent log archiving, faster storage, automation, extra environments, and more frequent recovery tests.

Know what you must recover

Rows are only part of a working database. Your recovery scope can also include:

schemas, indexes, constraints, and stored code;
roles, grants, and authentication settings;
database configuration and extensions;
transaction logs and backup catalogs;
encryption keys and credentials;
application configuration, connection changes, and recovery procedures.

A database-native backup may omit configuration files outside the database. PostgreSQL write-ahead log archiving, for example, records database changes but not manual edits to its configuration files.

Record every dependency. A restored data directory is not useful if the database version, encryption key, extension, or application connection path is missing.

Choose the backup form by recovery need

Logical backup

A logical backup represents database objects and data as portable definitions, statements, or exported records. It can support selective restoration and migration between compatible environments.

Logical recovery must recreate objects and load data. That work can take longer for a large database. The export may also omit server-level settings unless you capture them separately.

Physical backup

A physical backup copies database files in the form the database engine uses. It often favors faster whole-system recovery and preserves engine-specific layout.

Physical recovery is more sensitive to engine version, platform, storage layout, and backup-tool rules. Copying live database files with a generic file tool can create an inconsistent backup.

Base backup plus transaction logs

Many database engines combine a base backup with an ordered stream of transaction logs. Recovery restores the base and replays logs to a chosen recovery target.

PostgreSQL uses write-ahead log files for this pattern. MySQL uses binary logs for point-in-time recovery. A missing required log breaks the recovery chain beyond that gap.

Full and incremental backup

A full backup stands as the starting copy for a recovery chain. An incremental backup captures changes since an earlier backup or recorded point.

Incremental backups can reduce transfer and storage, but they add dependencies. Keep the full backup, every required increment, and the metadata that describes their order.

Consistency is a database property

A recoverable backup must represent a state the database engine can make consistent. Use database-native tools, supported snapshot coordination, or a documented quiesce procedure.

For a transactional database, the engine may use logs during restore to finish committed work and undo incomplete work. The exact mechanism belongs to the engine and backup method.

Application consistency can require more. If one business operation spans a database and an external object store, a database-only restore may leave those systems at different points.

Retention is a recovery design

Retention decides which recovery points survive long enough to meet business and legal needs. It must account for the entire dependency chain.

Keep recovery material outside the likely production failure boundary. CISA recommends offline, encrypted backups of critical data and regular tests of their availability and integrity.

Protect backup confidentiality and integrity. Restrict deletion, record access, preserve required keys, and monitor failures. A copy that an incident can erase with production data is a weak recovery boundary.

Recovery is a controlled sequence

A sound recovery run follows an explicit order:

Classify the incident and select a trusted recovery point.
Preserve evidence when corruption or compromise may be involved.
Build or isolate a clean recovery target.
Restore the base backup and apply every required increment or log.
Stop at the intended point and prevent unintended writes.
Validate engine health, schema, critical data, permissions, and application behavior.
Reconcile changes that occurred outside the recovered database.
Cut applications over, monitor them, and record achieved RPO and RTO.

Do not restore over the only remaining copy until the procedure explicitly permits it. An isolated target gives you room to validate, investigate, or choose a different recovery point.

Test the claim you plan to make

Checksums and backup-tool validation can detect some damaged media. Only a restore test exercises retrieval, dependencies, credentials, engine startup, log replay, and validation.

Use realistic data volume and an environment that represents production constraints. Time every stage. Confirm that the recovered application can perform its critical work, not only that the database process starts.

Test several cases: whole-database loss, accidental deletion, a point before corruption, and loss of a backup component. Record the result, fix the runbook, and repeat after material changes.

Limits

Backup does not provide continuous availability. Replication and failover can reduce outage time, but replicas can copy accidental deletion or corruption.

Backup does not replace security. A recovered database can still contain malicious changes, vulnerable software, or stolen credentials.

Backup also does not settle business reconciliation. Point-in-time recovery may intentionally discard later transactions. The application owner must decide how to recover or account for them.