Database Management · Beginner level

Beginner level · Module 5: Database Care: Indexes, Backups and Access · Lesson 2 of 3

Backups and Restores

Make regular backups, store them safely and prove that they can be restored.

In this lesson you will

  • Back up and restore a SQLite or PostgreSQL database
  • Plan backup frequency, storage and testing

Key concepts

backuprestoreoff-site copyrestore test

The lesson

A backup is a copy of the database from which you can recover after a mistake, a failed disk, theft or ransomware. For SQLite, use the tool's backup command or copy the file while it is not being written; for PostgreSQL, pg_dump creates a backup file.

Decide how much data you can afford to lose — a day? an hour? — and back up at least that often. Keep several copies, at least one stored somewhere else, such as an encrypted external drive or cloud storage.

A backup you have never restored may not work. Restore into a separate database regularly and check the data.

Step by step

  1. Back up the school fees database.
  2. Delete some rows by mistake on purpose.
  3. Restore the backup into a new database and check the rows.
  4. Write a backup schedule.

Practice activity

Create a backup, restore it into an empty database and write a backup plan for the school.

Expected result: The restored database matches the original and the plan states frequency, storage and testing.

Check your understanding

  1. Why keep a backup off-site?

    Show answer

    So a fire, theft or failure at the main location does not destroy every copy.

  2. Why test restores?

    Show answer

    To be sure the backups actually work.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.