Theory
Friday, 4:55 pm: back up everything
Results week ends. The exam cell's parting order: "one file with the WHOLE database: students, marks, the audit log, the trigger, everything. And show us it actually restores."
Last lesson you dumped one table. Scaling up is not a loop over tables: you would forget the trigger, the indexes, the order.
The shell has a one-word answer: .dump with no argument: the entire database as one replayable SQL script.
Theory
The full recipe book
One table's dump was a recipe card. The bare .dump writes the whole recipe book: every table, every index, every trigger, in dependency order, with BEGIN TRANSACTION at the top and COMMIT at the end.
Why the transaction wrapper? So a restore cooks all or nothing: if replay fails at line 900, the new database is not left half-cooked. Your Unit 1 transactions lesson, working overtime.
Follow along
Full backup, the standard sequence
- sqlite3 college.db Open the shell on the database to back up.
- .output full_backup.sql Send everything the shell prints into the file.
- .dump No argument = the ENTIRE database: all tables, data, indexes, triggers, wrapped in one transaction.
- .output stdout then .quit Restore screen printing, leave the shell. full_backup.sql is your recipe book.
- One-liner alternative (no shell session): sqlite3 college.db .dump > full_backup.sql The terminal redirect does the .output job; handy in scripts and cron jobs.
Theory
Some tables only, and the restore
Selected tables: .dump students marks writes just those two (any number of names). Note: other tables' triggers and indexes are not included; only a bare .dump guarantees everything.
Restore turns SQL text back into a database:
- From the terminal:
sqlite3 restored.db < full_backup.sql - Inside the shell:
.read full_backup.sql
Restore into a fresh, empty file: replaying CREATE TABLE into a database that already has those tables stops with an error.
Quiz
You must move ONLY the students and marks tables (with data) to a teammate's machine as readable SQL. Which command produces the file?
- .dump students marks (after .output transfer.sql)
- .dump with no argument
- .schema students marks
- .read students marks
Show the answer
.dump students marks (after .output transfer.sql)
.dump accepts a list of table names and exports exactly those, structure plus data. A bare .dump would also drag along the audit log and everything else (works, but ignores the ONLY). .schema gives structure without data, and .read is the RESTORE direction: it consumes SQL files rather than producing them. Matching the command to the direction of travel is the whole question.
Think first
Predict the restore
A student runs sqlite3 college.db < full_backup.sql, restoring INTO THE ORIGINAL database that still contains all its tables. Before tapping: what happens, and what should they have done?
Show the answer
The replay starts and immediately hits CREATE TABLE students... where students already exists: error, and the transaction wrapper rolls the attempt back. Nothing is lost, but nothing is restored either.
Correct practice: restore into a fresh file: sqlite3 restored_college.db < full_backup.sql, then verify with a SELECT, and only then swap files if replacing was the goal. Restores are rehearsed on empty stages.
Watch out
Backup rules that save careers
A backup you never restored is a hope, not a backup: always test-replay into a scratch file.
Partial dumps forget triggers: .dump marks does not carry the audit trigger on marks_log; whole-database work wants the bare .dump.
.read paths are relative to where you launched sqlite3: 'file not found' usually means you are standing in the wrong folder, not that the file is gone.
Theory
Where this meets the rest of the subject
The dump file is pure SQL text, so everything from Unit 1 applies inside it: you can open it and read the CREATE TRIGGER you wrote, the INSERTs your transaction committed. In Unit 3, Python will automate this workflow (iterdump() exists precisely for programmatic dumps), and the CSV lessons next give you the OTHER export format: data-only, for spreadsheets rather than databases.
Summary
Key takeaways
- Bare .dump = the entire database as SQL: tables, data, indexes, triggers, in one transaction.
- .dump name1 name2 exports selected tables only (their triggers elsewhere are not included).
- Standard sequence: .output file.sql, .dump, .output stdout; or one-line: sqlite3 db .dump > file.sql.
- Restore with sqlite3 fresh.db < file.sql, or .read file.sql inside the shell, into an EMPTY database.
- The transaction wrapper makes a restore all-or-nothing.
- Test every backup by actually restoring it once.
- Memory hook: the full recipe book, rehearsed on an empty stage.