How to Backup and Restore the n8n SQLite Database

n8n SQLite errors / Leave a Comment

Step by Step Guide Backup and Restore the n8n SQLite Database

 

For a complete guide on managing SQLite in n8n, including errors, performance, and migration, check out our n8n SQLite pillar guide.

 

Quick Diagnosis

  1. Stop n8n – docker stop n8n or pm2 stop n8n.
  2. Copy the DB file – cp ~/.n8n/.n8n.sqlite ./backup/n8n-$(date +%F).sqlite.
  3. Start n8n – docker start n8n or pm2 start n8n.
  4. To restore, stop n8n, replace the file with the backup, then start n8n again.
  5. Verify with n8n start --tunnel and check the UI; the workflow list should match the backup.

Why a Dedicated Backup/Restore Guide?

The n8n SQLite error overview explains common failure modes, but backing up the database safely is a separate operational task. A corrupted or lost .sqlite file means lost workflows, credentials, and execution history. This guide walks you through production‑grade backup and restore procedures that avoid race conditions, preserve file permissions, and work whether you run n8n in Docker, via PM2, or as a system service. Find out how to manage disk space in n8n, prevent corruption, and optimize SQLite performance in n8n.

Prerequisites

Requirement Detail Why it matters
**n8n version** ≥ 1.0 (SQLite is still the default storage) API and CLI flags differ before v1.0
**Access rights** OS user that runs n8n must own ~/.n8n/.n8n.sqlite Prevents “permission denied” errors during copy
**Backup destination** Separate filesystem or remote bucket (e.g., S3, GCS) Guarantees durability beyond a single host failure
**Optional** sqlite3 binary installed (apt install sqlite3 or brew install sqlite3) Enables hot‑backup via SQLite’s own API

EEFA note: Never back up the DB while n8n is writing to it *unless* you use SQLite’s online backup API (.backup). A live copy can lead to a corrupted backup file.

Backup Strategies

2.1 Manual File Copy (Offline)

Best for small self‑hosted installations or one‑off backups where a brief downtime is acceptable.

# Stop n8n (Docker example)
docker stop n8n

# Create a timestamped backup folder
mkdir -p /var/backups/n8n
BACKUP=/var/backups/n8n/n8n-$(date +%F-%H%M).sqlite

# Copy the DB file
cp ~/.n8n/.n8n.sqlite "$BACKUP"

# Optional: Verify checksum
sha256sum "$BACKUP" > "$BACKUP.sha256"

# Restart n8n
docker start n8n

2.2 Hot Backup with sqlite3 (Online)

Use when downtime is not an option. SQLite’s .backup command creates a consistent snapshot while the database remains active.

# Run as the same user that owns the DB file
sqlite3 ~/.n8n/.n8n.sqlite \
  ".backup '/var/backups/n8n/n8n-$(date +%F-%H%M).sqlite'"

EEFA Warning – If the underlying filesystem does **not** support atomic rename (e.g., async NFS), the backup may still be inconsistent. Prefer a local ext4/xfs volume or a cloud block store with strong consistency guarantees.

2.3 Docker‑Native Volume Snapshot

For deployments that use a named Docker volume (n8n-data). This method copies the DB from a temporary container, avoiding any impact on the running n8n instance. Learn recovery steps for a corrupt SQLite database and other storage-related issues in n8n.

docker run --rm \
  -v n8n-data:/data \
  -v $(pwd)/backup:/backup \
  alpine \
  sh -c "cp /data/.n8n.sqlite /backup/n8n-$(date +%F-%H%M).sqlite"

Verify the Backup

Check Command Expected outcome
File size stat -c %s backup.sqlite Matches the live DB size (± few KB)
SQLite integrity sqlite3 backup.sqlite "PRAGMA integrity_check;" Returns ok
Workflow count sqlite3 backup.sqlite "SELECT COUNT(*) FROM workflow_entity;" Same as live DB (run before/after)
Checksum sha256sum backup.sqlite vs stored checksum Identical hash

If any check fails, repeat the backup using the **online method** (2.2) to avoid a partially‑written file.

Restore Procedures

4.1 Manual Restore (Same Host)

Replace the corrupted or lost DB with a known good backup on the original host.

# Stop n8n
docker stop n8n   # or pm2 stop n8n

# Replace the DB file
cp /var/backups/n8n/n8n-2025-12-23.sqlite ~/.n8n/.n8n.sqlite

# Ensure correct ownership (Docker runs as root by default)
chown 1000:1000 ~/.n8n/.n8n.sqlite   # adjust UID/GID as needed

# Restart n8n
docker start n8n

Post‑restore sanity check – Open the n8n UI, go to *Workflows*, and verify that the expected number of workflows appears. Optimize performance and prevent storage issues like corruption and disk full errors in n8n SQLite.

4.2 Restoring into a New n8n Instance (Migration)

Use this when moving to a fresh host or rebuilding the container.

# Create a fresh n8n container with an empty volume
docker compose up -d n8n

# Copy the backup into the new volume before the first start
docker run --rm \
  -v new-n8n-data:/data \
  -v $(pwd)/backup:/backup \
  alpine \
  sh -c "cp /backup/n8n-2025-12-23.sqlite /data/.n8n.sqlite && \
         chown 1000:1000 /data/.n8n.sqlite"

After the copy, start the container (docker compose up -d n8n) and verify the UI as above.

Automating Daily Backups

A nightly cron job (or systemd timer) can run the **online backup** during low‑traffic hours.

# /etc/cron.d/n8n-sqlite-backup
30 2 * * *  n8nuser  /usr/bin/sqlite3 /home/n8n/.n8n/.n8n.sqlite \
    ".backup '/var/backups/n8n/n8n-$(date +\%F-\%H\%M).sqlite'" \
    && sha256sum /var/backups/n8n/n8n-$(date +\%F-\%H\%M).sqlite \
       > /var/backups/n8n/n8n-$(date +\%F-\%H\%M).sha256

Rotation policy – Keep 30 daily, 12 weekly, and 6 monthly backups. A simple find command purges old files:

find /var/backups/n8n -type f -name 'n8n-*.sqlite' -mtime +30 -delete

Troubleshooting Common Issues

Symptom Likely cause Fix
“SQLITE_BUSY: database is locked” during backup Using manual cp while n8n is running Switch to the **online .backup** method (2.2) or stop n8n briefly.
Restored DB shows 0 workflows Restored file is empty or corrupted Verify integrity with PRAGMA integrity_check;. Re‑run backup using a fresh snapshot.
Permission denied when copying Docker volume owned by root, host user is non‑root Use docker exec with --user root or adjust chown after copy.
Checksum mismatch after transfer to remote storage Transfer was interrupted (e.g., S3 multipart) Enable MD5 verification on the upload client; re‑upload if mismatch.
n8n fails to start after restore SQLite version mismatch (backup from SQLite 3.41, runtime uses 3.34) Ensure the runtime includes the same SQLite library version, or export/import via .dump/.read.

Best‑Practice Checklist

  • [ ] Stop n8n before any *offline* copy.
  • [ ] Use **SQLite’s .backup API** for hot backups whenever possible.
  • [ ] Store backups **off‑host** (S3, GCS, Azure Blob) with versioning enabled.
  • [ ] Verify each backup with PRAGMA integrity_check; and a checksum.
  • [ ] Automate retention with a **rotation policy** (30‑12‑6).
  • [ ] Test **restore** at least quarterly in a staging environment.
  • [ ] Document the backup schedule and location in your **run‑book**.

Must Read

Leave a Comment

Your email address will not be published. Required fields are marked *