# VACUUM INTO: Atomic SQLite Snapshots That Work

> cp on a live database corrupts backups. VACUUM INTO snapshots consistently with zero downtime. The cron plus restore drill inside.

Source: https://boringpush.com/blog/backup-sqlite-vacuum-into-snapshots

## A backup that restores to garbage

The obvious SQLite backup is a nightly file copy.

It is clean, simple and wrong on a live database. If a write lands between the first and last byte, the snapshot fails its own integrity check.

A file copy is a photograph of a moving car. The shape is right. The parts are unusable.

Back up through the database engine. Never through the filesystem.

The engine knows transactions. A copy only knows bytes.

## Ranked by honesty

| Method | Consistent? | Downtime? | Verdict |
|---|---|---|---|
| Copy the live file | No, torn pages | None | Garbage with timestamps |
| Copy with the app stopped | Yes | An outage every night | Correct and absurd |
| SQLite's online backup | Yes | None | Correct, needs scripting |
| `VACUUM INTO` snapshot | Yes, and compact | None | Correct, smaller files |
| Replicated volume | Crash-consistent | None | Restore may need replay |

The snapshot approach wins on three counts.

It is one statement. It needs no downtime. And it rebuilds the file compactly, so snapshots are often smaller than the live database.

Smaller backups transfer, verify and restore faster.

## What to do

Snapshot every day, while the app serves.

Keep several recent copies, and prune the rest. A disk that fills silently kills the app.

Keep one copy somewhere your server cannot delete. A dead disk, a compromised box or a bad command can take everything on the machine.

Then drill it. A backup you never restored is a rumor.

## The restore drill

Once a month, restore the latest snapshot to a scratch location.

- Stop the app, or point a scratch copy of it at the restored file.
- Run an integrity check and confirm it says ok.
- Load a page that reads real data, like a dashboard or an order list.
- Note how long it took, so the next drill has a number to beat.

Once a quarter, do it from the off-site copy only.

If any step surprises you, you found the problem on a quiet day.

## What it costs

A little disk for the copies. About half an hour per drill.

Against one data-loss event, which can end a solo business, it is the cheapest money in operations.

## Backups earn the name at restore

Three habits make a backup real.

Check the snapshot's integrity every night. SQLite's own integrity check takes seconds and catches corruption.

Restore one into a scratch copy every month. Start the app against it. That catches permissions, paths and compatibility.

Test the off-site copy every quarter. Restore from it alone.

None of these cost much. Skipping them costs everything the day you need the backup.

## Keep a copy your server cannot delete

The off-site copy goes to object storage, like R2 or S3.

It uses its own credentials, separate from the app's. Whatever can reach your app cannot necessarily reach your backups.

If your bucket supports object lock, set a retention period. Snapshots then cannot be deleted before it ends.

## Know this first

The kit does not run your restore drills. It does not check snapshot integrity for you either.

A backup you never restored is a rumor. Restore one now and then.

## Why BoringPush is the way to do this

Your data is backed up before you think about it.

BoringPush takes a consistent snapshot every day while the app keeps serving. One command, `bp db backup`, takes another on demand. `bp db backup --offsite` sends a copy off the server.

`bp status` shows the latest.

No cron to write. No downtime to schedule.

## FAQ

### Is a VACUUM INTO backup enough without WAL mode?

They solve different problems. WAL mode lets readers and writers work at the same time; snapshots give you consistent copies. Backups also work without WAL, but production should run WAL regardless.

### How long do snapshots take on large databases?

Roughly in proportion to size. Gigabytes take minutes on fast disks, and the app keeps serving. Run them in a quiet window anyway, and watch for snapshot times that keep growing, which signal data worth archiving.

### Should snapshots be encrypted?

Off-site copies: yes. Encrypt before upload and keep the key in your password manager, not beside the data. Local copies are usually fine with strict file permissions, unless laptops travel with them.

### What about point-in-time recovery?

Nightly snapshots give you 24-hour granularity. Continuous WAL shipping gives finer recovery, but it needs extra tooling that BoringPush does not ship. Consider it once a loss inside the 24-hour window would really hurt, such as payment histories.

### How many copies should I keep?

Think 3-2-1: three copies (live, local snapshot, off-site), two kinds of storage, one location away from production. For a solo app that costs one storage bucket and one command beyond the basics.

### When do managed-database backups beat this?

When your team lacks the discipline to drill restores, or when auditors want a vendor's certified process. Disciplined solos can keep the file and the savings. Know which operator you are.

## Keep reading

- [SQLite in Prod Is Fine. You're Just Missing 3 Settings](/blog/sqlite-prod-wal-backups-vacuum) — the routine this snapshot slots into
- [Next.js SQLite SaaS Boilerplate (Prod WAL Guide)](/blog/nextjs-sqlite-saas-boilerplate) — the setup producing data worth backing up
- [SQLite in Prod Won't Eat Your Data (3 Rules)](/blog/sqlite-prod-playbook) — the founding sketch this routine operationalizes