Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 33 ยท Using SQLite from Code

Backup, VACUUM and Maintenance

Back up a live SQLite database safely with .backup and VACUUM INTO, reclaim space with VACUUM, run integrity_check and avoid the file-copy mistakes.

A SQLite database is a single file, which makes it wonderfully easy to move around and wonderfully easy to damage. Copying that file while a program is writing to it can produce a backup that looks fine and fails to open a month later. And a file that grows during busy periods does not shrink by itself when you delete data. Both problems have simple, built-in answers.

This lesson covers the safe ways to back up a database that is in use, how VACUUM and auto_vacuum reclaim space, how to check a file for damage with PRAGMA integrity_check, and what actually causes corruption. The sqlite3 shell commands and file-based features cannot run in the browser, so those parts are read-only; the page-level behavior of VACUUM and the integrity checks can be run live against an in-memory database.

Three chores for a healthy file

Backup A consistent copy, even while in use.
Vacuum Give unused space back.
Check Prove the file is not damaged.
1 / 2

Why copying the file is dangerous

When a transaction commits, SQLite does not write everything to the main .db file in one atomic step. It uses a rollback journal (a -journal file) or, in WAL mode, a write-ahead log (the -wal and -shm files). If you copy only app.db with cp or a file-sync tool while a write is in progress, you can end up with:

  • a main file that is half-updated, with pages from two different moments in time;
  • a missing -wal file, so the most recent committed transactions are absent from the copy;
  • a -journal file left behind that, copied without its partner, causes SQLite to "recover" the wrong way on the next open.

Copying is safe in only two situations: no process has the database open, or you copy the main file together with its -wal and -shm files at a moment when nothing is writing. For everything else, use one of the next two tools.

Never delete a -journal or -wal file just because it looks like leftover clutter. If it belongs to a database that was not closed cleanly, it holds committed data that SQLite needs to replay. Removing it can lose transactions or corrupt the database.

Online backup with .backup

The sqlite3 shell has a .backup command built on SQLite's online backup API. It copies the database page by page while respecting locking, so the result is a consistent snapshot even if another process is writing during the copy. Applications can use the same API through their language driver (Python's Connection.backup(), for instance).

TEXT
$ sqlite3 app.db
sqlite> .backup main backup-2026-11-08.db
sqlite> .quit

Or in a single shell line, which is easy to put in a scheduled job:

TEXT
sqlite3 app.db ".backup 'backup-2026-11-08.db'"

In WAL mode readers do not block the writer, so the backup can run while the application keeps working. In rollback-journal mode a long backup can briefly conflict with writers; SQLite handles this, and the copy restarts if the source changes mid-way.

VACUUM INTO: a compact copy

VACUUM INTO 'file' writes a brand new, defragmented database file containing the same data, and leaves the original untouched. It is also a good backup method, with a bonus: the copy has no free pages and is usually smaller.

TEXT
sqlite> VACUUM INTO 'compact-copy.db';

The target file must not already exist (or must be empty). It runs inside a read transaction, so it sees one consistent snapshot. Compared with .backup, it rewrites everything so it takes more CPU and I/O, but it also repairs fragmentation in the copy.

Rules of thumb for backups:

  • Do them from a scheduled job, and keep several generations, not only the latest.
  • Store the copy on a different disk or machine; a backup beside the original dies with it.
  • Test a restore from time to time: open the backup and run PRAGMA integrity_check on it.
  • A restore is just placing the backup file where the application expects it, while no process has the database open.

Free pages and VACUUM

When you delete rows, SQLite does not return the space to the operating system. The pages become free pages on an internal free list and are reused by later inserts. The file stays the same size. You can see this in the page counters: page_count is the total number of pages, and freelist_count is how many of them are free.

SQL Deleting rows leaves free pages

Press Run (or Ctrl + Enter) to see the result here.

After the delete the table holds 100 rows, but the file still has all of its pages: most of them are now free. If new rows arrive they will fill those pages first, so the file does not grow until the free pages run out. That is why a database that has once been big stays big.

VACUUM rebuilds the database file from scratch, copying the live data into a fresh file and then replacing the original. The free pages disappear and the file shrinks. It also defragments tables and indexes, which can help scans a little.

SQL VACUUM gives the free pages back

Press Run (or Ctrl + Enter) to see the result here.

After VACUUM the free list is empty and the page count has collapsed, while the 100 remaining rows are untouched. Things to know before you run it on a real file:

  • It needs up to about twice the database size in free disk space while it works, because it builds a new copy.
  • It takes an exclusive lock on the database, so other connections are blocked during the rebuild.
  • It cannot run inside a transaction.
  • It can change rowid values of tables without an INTEGER PRIMARY KEY, so never rely on implicit rowids as stable ids. Declared integer primary keys are preserved.
  • Running it constantly is pointless. Do it after a big purge, or when freelist_count is a large share of page_count.

auto_vacuum

PRAGMA auto_vacuum changes how free pages are treated. There are three modes: NONE (0, the default), FULL (1) and INCREMENTAL (2). With FULL, SQLite moves free pages to the end of the file and truncates it at every commit. With INCREMENTAL, free pages stay on the list until you run PRAGMA incremental_vacuum(N), which releases up to N pages.

SQL Setting auto_vacuum before creating tables

Press Run (or Ctrl + Enter) to see the result here.

The PRAGMA incremental_vacuum line prints one empty row for every page it releases, so expect a block of blank rows in the output; the last result shows the free list is now empty. The mode must be chosen before the first table is created (or followed by a full VACUUM to apply it to an existing file). Auto-vacuum has a cost: it adds bookkeeping pages and can fragment the file, so most applications stay on NONE and run an occasional VACUUM instead.

Checking for damage

PRAGMA integrity_check reads every page, verifies the structure of tables and indexes and makes sure index entries match table rows. It returns a single row with the text ok when everything is fine, or a list of problems (by default up to 100). PRAGMA quick_check does the same without verifying that index contents match their tables, so it is faster and good for startup checks. PRAGMA foreign_key_check is separate: it reports rows that violate foreign keys, as covered in Foreign Keys.

SQL A healthy database reports ok

Press Run (or Ctrl + Enter) to see the result here.

Run the check after restoring a backup, after a crash or power cut, and from a scheduled job on important databases. A result other than ok means data is at risk: restore the most recent good backup rather than keep writing to the damaged file.

What corrupts a SQLite database

SQLite is very careful; real-world corruption almost always comes from outside it. The usual causes:

  1. Copying or deleting files while in use, as above, including deleting a hot journal or WAL.
  2. Network filesystems. SQLite relies on the file locking of the operating system, and many network shares (NFS, SMB, cloud-sync folders) implement it poorly or not at all. Keep the database on a local disk.
  3. Two programs with different locking, or several processes on a broken lock setup, such as a container volume that does not share locks.
  4. Disks or controllers that lie about flushing, so a power failure leaves the file partly written. This is rare with PRAGMA synchronous at its default for WAL (FULL is safest).
  5. Memory or storage faults and bad RAM, and software bugs that write into SQLite's memory (for example a stray pointer in C code).
  6. Using a file descriptor after fork(), or opening the same file after another process closed it, on POSIX systems.
  7. Cloud-sync tools (Dropbox, OneDrive and friends) that copy the main file and the WAL at different moments.

The prevention list follows from the causes: use SQLite's backup tools, keep the file on a local disk, let one set of processes share it, keep synchronous at a safe value, and check integrity regularly.

A simple maintenance routine

For a small to medium application, this is plenty:

  • Nightly: .backup to another location, then PRAGMA quick_check on the copy.
  • Weekly: PRAGMA optimize (or run it when closing connections) so the query planner keeps good statistics; see EXPLAIN QUERY PLAN and the Optimizer.
  • After a big delete: VACUUM, if freelist_count is a large part of page_count.
  • In WAL mode: make sure checkpoints can complete, so the -wal file does not grow without bound; see Concurrency, Locking and WAL.
  • Occasionally: restore a backup into a scratch folder and open it, to prove the process works.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. You delete most rows of a large table. What happens to the database file size?

    Deleted rows turn their pages into free pages on an internal list. The file keeps its size until you run VACUUM (or use auto_vacuum).
  2. 2. Which is the safest way to back up a database that an application is using right now?

    .backup uses the online backup API and VACUUM INTO reads one consistent snapshot. A plain file copy can capture a half-written state or miss the WAL.
  3. 3. What does PRAGMA integrity_check return for a healthy database?

    A healthy database yields one row with the text ok. Anything else is a list of problems that were found.
  4. 4. Which statement about VACUUM is true?

    VACUUM writes a new compacted file, so plan for up to about double the database size in free space, and it cannot run inside a transaction.
  5. 5. Why is putting a SQLite database on a cloud-synced or network folder risky?

    SQLite depends on correct file locking and on the files being copied together. Sync tools and many network filesystems break both assumptions.

Summary

  • Do not back up a live database with a plain file copy; use .backup (online backup API) or VACUUM INTO for a consistent snapshot.
  • Deleted data leaves free pages: the file does not shrink until VACUUM rebuilds it, which needs spare disk space and an exclusive lock.
  • auto_vacuum (NONE, FULL, INCREMENTAL) must be set before tables exist; most apps use an occasional VACUUM instead.
  • PRAGMA integrity_check returns ok for a healthy file; quick_check is the faster, lighter version.
  • Corruption comes from outside SQLite: copying live files, deleting journals or WAL, network or sync folders, lying disks and faulty hardware.
  • Keep several backups on another disk and test restoring them.

Next: Project: A Notes App Database, putting the whole track to work in a small real schema.