Backup Database

Backups are your safety net against mistakes, corruption, and hardware failure.

For SQLite, the best method depends on whether the database is actively being written.

Core Concepts

flowchart TD
  A[Need backup] --> B{Database actively in use?}
  B -->|Yes| C[Use sqlite3 .backup]
  B -->|No| D[File copy can work]
  C --> E[Verify backup file]
  D --> E

Method	Best for	Key note
.backup	Online/in-use databases	Consistent snapshot via SQLite engine
File copy (cp)	Offline databases	Ensure no active writes during copy

Online Backup

# Create a consistent backup while using SQLite shell backup API.
sqlite3 app.db ".backup app_backup.db"

Expected output
Usually no output on success; app_backup.db is created.

Offline Copy

# Use only when database is not being written.
cp app.db app_backup.db

Expected output
No output on success; backup file is created.

Code Examples

-- Setup tiny demo database.
CREATE TABLE notes_bk (
  id    INTEGER PRIMARY KEY,
  title TEXT NOT NULL
);

INSERT INTO notes_bk (title)
VALUES ('backup me');

Expected output
Demo table and one row created in source database.

# Create backup using SQLite backup command.
sqlite3 app.db ".backup app_backup.db"

Expected output
app_backup.db exists as backup snapshot.

# Verify backup by querying it directly.
sqlite3 app_backup.db "SELECT count(*) AS row_count FROM notes_bk;"

row_count
1

SQLite-Specific Nuances

SQLite Nuance

The .backup command is SQLite-aware and safer than plain file copy for active databases.

Prefer .backup for production-style workflows.

Common Pitfalls / Best Practices

Pitfall

Creating backups but never verifying they can be read.

An unverified backup is a false sense of safety.

Best Practice

After every backup, run one quick validation query against the backup file.

Quick Challenge

Create main_backup.db from main.db and verify table count with one query.

View Solution

sqlite3 main.db ".backup main_backup.db"
sqlite3 main_backup.db "SELECT count(*) AS object_count FROM sqlite_schema WHERE type IN ('table','view');"

object_count
Number of tables/views in backup