SQLite Basics: Create Compact Database Copies with VACUUM INTO

VACUUM INTO writes a compact copy of a SQLite database to a new file while leaving the source database unchanged. It is useful for rehearsing exports and checking a snapshot before using it elsewhere. The examples below build toy databases inside a fresh scratch directory, not copies of personal or production data.

VACUUM INTO was added in SQLite 3.27.0. Use a maintained runtime; the historical minimum was not tested here. SQLite 3.27.0 release notes.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. Each invocation starts with a fresh in-memory main database and prints pipe-separated rows, showing missing values as NULL. It does not escape embedded pipes or newlines, so it is a teaching runner, not a general SQL shell or CSV exporter. Put each statement’s ending semicolon at the end of a line, as shown; do not put two complete statements on one line.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

The SQL below also creates the named database files in this scratch directory. Those files survive when the runner exits; the main in-memory database does not. Run each example once before cleanup, or begin again in a fresh scratch directory. Do not substitute an existing database or backup path.

1. Export a snapshot and distinguish it from later changes

Save as export-notes-snapshot.sql:

CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT NOT NULL);
INSERT INTO notes VALUES (1, 'Outline'), (2, 'Review');
VACUUM INTO 'notes-copy.sqlite';
UPDATE notes SET title = 'Revised outline' WHERE id = 1;
ATTACH DATABASE 'notes-copy.sqlite' AS snapshot;
SELECT 'source', id, title FROM main.notes ORDER BY id;
SELECT 'snapshot', id, title FROM snapshot.notes ORDER BY id;
DETACH DATABASE snapshot;

Run:

python3 run-sql.py export-notes-snapshot.sql

Output:

source|1|Revised outline
source|2|Review
snapshot|1|Outline
snapshot|2|Review

The copy retains Outline even after the source changes to Revised outline. It represents the database at the time of the export, not a continuously synchronized mirror. The source is in memory in this example; the new snapshot is a real file in the scratch directory.

Choose an output path that does not exist, or an existing empty file. VACUUM INTO refuses a nonempty destination rather than replacing it. Consequently, rerun this example only in a fresh scratch directory or after removing its disposable copy. Never delete an existing backup just to make a demonstration command succeed.

2. Copy a selected attached database

Save as export-attached-catalog.sql:

ATTACH DATABASE 'working.sqlite' AS working;
CREATE TABLE working.catalog (
    sku TEXT PRIMARY KEY,
    description TEXT NOT NULL
);
INSERT INTO working.catalog VALUES
    ('pen', 'Blue pen'),
    ('pad', 'Grid pad'),
    ('old', 'Discontinued item');
DELETE FROM working.catalog WHERE sku = 'old';
VACUUM working INTO 'catalog-copy.sqlite';
ATTACH DATABASE 'catalog-copy.sqlite' AS exported;
SELECT 'source', COUNT(*) FROM working.catalog;
SELECT sku, description FROM exported.catalog ORDER BY sku;
DETACH DATABASE exported;
DETACH DATABASE working;

Run:

python3 run-sql.py export-attached-catalog.sql

Output:

source|2
pad|Grid pad
pen|Blue pen

The schema name working selects the attached source rather than the runner’s main database. The copy contains only the remaining catalog entries. This is handy when one connection manages several files and an export must name exactly which one is being copied.

The example checks logical contents, not a promised number of bytes saved. A tiny database may occupy the same number of pages before and after compaction. Removing deleted content from the new copy also does not erase the source file, old backups, filesystem snapshots, or other copies of the data.

3. Check the copied schema and rows

Save as verify-vacuum-copy.sql:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT NOT NULL UNIQUE,
    price_cents INTEGER NOT NULL CHECK (price_cents >= 0)
);
CREATE INDEX products_price ON products(price_cents);
BEGIN;
INSERT INTO products VALUES (1, 'pen', 250), (2, 'pad', 600);
COMMIT;
VACUUM INTO 'checked-copy.sqlite';
ATTACH DATABASE 'checked-copy.sqlite' AS checked;
PRAGMA checked.quick_check;
SELECT name FROM checked.sqlite_master
WHERE type = 'index' AND name = 'products_price';
SELECT COUNT(*), SUM(price_cents) FROM checked.products;
DETACH DATABASE checked;

Run:

python3 run-sql.py verify-vacuum-copy.sql

Output:

ok
products_price
2|850

The checks report a structurally sound toy copy, the expected named index, and two rows totaling 850 cents. Checking more than file existence catches an export of the wrong database or an unexpected schema. Real restore testing should also open the copy with the consuming application and verify representative data.

COMMIT occurs before VACUUM. VACUUM cannot run inside an active transaction on the connection; unfinished statements can also prevent it. This runner fetches each result fully and uses SQLite autocommit except during an explicit BEGIN/COMMIT block. A production application must deliberately manage its own transaction and cursor state.

The VACUUM reference documents destination restrictions and snapshot behavior. An interrupted export can leave an incomplete or corrupt output, so check successful completion and validate before relying on it. The quick_check documentation explains that this faster check is less exhaustive than integrity_check. Neither check proves a backup meets the application’s recovery requirements.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py export-notes-snapshot.sql export-attached-catalog.sql verify-vacuum-copy.sql
rm -- notes-copy.sqlite working.sqlite catalog-copy.sqlite checked-copy.sqlite
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.