AdvancedLesson 27 of 306 min read

Backups, Restores and Least Privilege

Take a backup, survive a committed accident by restoring it, verify the restore with counts and totals, and see why accounts should have only the access they need.

In this lesson you will

  • Describe a good backup routine
  • Restore and verify a backup
  • Apply least privilege
  • Write a recovery note

About 28 minutes

The delete that ran on the real shop

On a quiet afternoon, someone connects to Fernleaf’s real database to try a clean-up query and forgets the WHERE clause. Or they include it, with the wrong status. The result is the same: the orders have gone. In lesson 9 you learned to preview changes and use transactions, but a committed mistake needs something else, a backup. And a backup you have never restored is only a hope. This lesson covers the two habits that protect a database from people and from accidents: restorable backups, and limited access.

What a good backup routine looks like

  • Take copies on a schedule that matches how much recent work you can afford to lose. If losing a day is acceptable, daily is enough. If an hour is too much, copy more often.
  • Use the database’s own backup method. Copying the file of a database that is in use can capture it half written. Real databases provide tools that produce a consistent copy.
  • Keep copies somewhere else. A backup on the same disk fails with the disk. Keep at least one copy off the machine.
  • Protect the copies. They hold all your data, so apply the same care to who can read them as to the live data.
  • Practise restoring. Restore into a separate place, check it and note how long it takes.

Backup, accident, restore

The program loads the Fernleaf database into a file, takes a backup with SQLite’s backup function, deletes the shipped orders from the live database and commits, which simulates the accident. Then it restores the backup into a new file and checks it, instead of simply trusting it. Last, it opens the backup read-only and tries to change it.

import sqlite3

src = sqlite3.connect("live.db")
src.executescript(open("shop.sql").read())            # the script from lesson 1
src.commit()
tables = ["customers", "products", "orders", "order_items"]
count = lambda db: {t: db.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0] for t in tables}
print("live database:", count(src))

bak = sqlite3.connect("backup.db")
src.backup(bak)                                        # a consistent copy
bak.close()

src.execute("DELETE FROM orders WHERE status = 'shipped'")   # a bad day
src.commit()
print("after the accident:", count(src))

# restore into a new file and test it before trusting it
restored = sqlite3.connect("restored.db")
sqlite3.connect("backup.db").backup(restored)
print("restored copy:", count(restored))
print("restore matches the original counts:", "yes" if count(restored) == {"customers": 6, "products": 8, "orders": 10, "order_items": 19} else "no")
print("integrity check:", restored.execute("PRAGMA integrity_check").fetchone()[0])
print("shipped revenue in the restore:", round(restored.execute(
    "SELECT SUM(oi.qty * p.price) FROM order_items oi JOIN orders o ON o.id = oi.order_id JOIN products p ON p.id = oi.product_id WHERE o.status = 'shipped'").fetchone()[0], 2))

# a read-only connection cannot change anything
ro = sqlite3.connect("file:backup.db?mode=ro", uri=True)
try:
    ro.execute("DELETE FROM customers")
    print("read-only delete: allowed")
except sqlite3.OperationalError as why:
    print("read-only delete refused:", why)
print("customers still in the backup:", ro.execute("SELECT COUNT(*) FROM customers").fetchone()[0])
live database: {'customers': 6, 'products': 8, 'orders': 10, 'order_items': 19}
after the accident: {'customers': 6, 'products': 8, 'orders': 3, 'order_items': 19}
restored copy: {'customers': 6, 'products': 8, 'orders': 10, 'order_items': 19}
restore matches the original counts: yes
integrity check: ok
shipped revenue in the restore: 257.0
read-only delete refused: attempt to write a readonly database
customers still in the backup: 6

The live database started with 6 customers, 8 products, 10 orders and 19 order items. After the accident the orders table held only 3 rows, and the order items stayed at 19, now pointing at orders that no longer exist. The restored copy has all 10 orders back, and every count matches the original. The checks went beyond counts: the database’s own integrity check says ‘ok’, and the shipped revenue worked out from the restored data is 257.0, the figure from the earlier lessons. Those are the signs of a backup that works. A count that matched but a total that did not would have been a warning.

Least privilege for people and programs

The second half of the program shows the idea of least privilege. The backup was opened in read-only mode, and the delete was refused with ‘attempt to write a readonly database’. The customers table still held 6 rows. Big databases go much further than a read-only file. They have separate user accounts, and the standard SQL commands GRANT and REVOKE give each account only the rights it needs. A reporting account might only read, a loader might insert into particular tables and only an administrator might drop a table. The exact commands and the kinds of rights differ between databases, so use your own documentation. SQLite itself has no user accounts, which is why the example uses a read-only connection.

A short safety checklist

  1. Decide how much data you can afford to lose, and how long you can afford to be without it.
  2. Automate backups, and alert yourself if one fails or is missing.
  3. Restore a backup to a test place at regular intervals, and check counts and a key total.
  4. Give every program and person the lowest access that lets them do their job.
  5. Keep a written recovery note, so that anyone on the team can follow it under stress.
Common mistake: untested backups

Having backups and never restoring one. When the day comes, you find the files are empty, or incomplete, or the password is missing. Practise before you need it.

A practice copy only

The database and the accident are made up, and everything ran on temporary files. Backup tools, user accounts and permission commands differ between databases, and retention and legal rules for keeping data differ by country, so check yours. This lesson is an outline of good habits and not a complete security guide.

Write a one-page recovery note for a database you use: where backups go, how often, who can restore, and the exact steps. Then do a restore test and time it.

Practice for this lesson

Check what you learned

Five quick questions. Pick an answer and you will see straight away whether it is right and why.

Why restore a backup as a test?

  1. It speeds up the database
  2. It removes old data
  3. Only a restored backup proves the backup works
  4. It sorts tables

The restore matched the counts and the revenue total.

Why use the database’s own backup method?

  1. It produces a consistent copy even while the database is in use
  2. It is smaller
  3. It needs no disk
  4. It avoids passwords

A raw file copy can be half written.

What does least privilege mean?

  1. Give everyone full access
  2. Remove all accounts
  3. Give each account only the access it needs
  4. Share one password

The read-only connection could not delete anything.

What did the integrity check and the revenue total add to the count check?

  1. More evidence that the restored data is complete and correct
  2. Nothing
  3. A faster restore
  4. A smaller file

Counts alone can match while values differ.

Why keep a backup off the machine?

  1. It makes restores faster
  2. A backup on the same disk can fail with the disk
  3. It saves space
  4. It is required by SQL

A separate place survives a machine failure.

Summary

  • A backup is only proven by a restore.
  • Use the database’s own backup method and keep copies elsewhere.
  • Check counts and a key total after restoring.
  • Give each account the lowest access it needs.

Next lesson

Lesson 28 turns to professional life: selling SQL work honestly.

Questions

How often should I back up?

As often as the amount of recent work you cannot afford to lose. Decide that number first, then schedule backups to meet it.

Does a transaction replace a backup?

No. A rollback only undoes uncommitted changes. A committed mistake needs a backup.

Do all databases have user accounts?

Most server databases do. SQLite does not, which is why the lesson used read-only access.

Progress is saved in this browser.