Introduction
When running DuckDB in production, data safety is paramount. Unlike traditional client-server databases like PostgreSQL or MySQL, DuckDB is an embedded analytical engine — its data lives entirely in local files. This means you need to design your own backup strategy, rather than relying on built-in automated backup mechanisms.
Figure: DuckDB backup architecture — three strategies working together: Snapshots, EXPORT DATABASE, and Compressed Archives
This article systematically covers DuckDB’s three core backup strategies with real-world production scenarios:
- Snapshot — Lightweight point-in-time recovery based on WAL
- EXPORT DATABASE — Structured SQL script and columnar format export
- Compressed Archive — Efficient compression and object storage integration
1. Snapshot: Lightweight Point-in-Time Recovery
1.1 How Snapshots Work
DuckDB’s snapshot feature is based on the Write-Ahead Log (WAL) mechanism. When you execute CREATE SNAPSHOT, DuckDB creates a read-only copy of the current database state. The key advantage is fast creation and low resource consumption, thanks to DuckDB’s Copy-on-Write strategy — only modified data pages are copied.
1.2 Creating and Managing Snapshots
-- Connect to the database
$ duckdb ecommerce.duckdb
-- Create a named snapshot
DuckDB> CREATE SNAPSHOT snap_20260828;
✓ Snapshot created: snap_20260828
-- List all snapshots
DuckDB> SELECT name, timestamp FROM duckdb_snapshot_order();
┌──────────────────┬─────────────────────┐
│ name │ timestamp │
├──────────────────┼─────────────────────┤
│ snap_20260828 │ 2026-08-28 10:00:00 │
│ snap_20260821 │ 2026-08-21 10:00:00 │
│ snap_20260814 │ 2026-08-14 10:00:00 │
└──────────────────┴─────────────────────┘
Figure: Terminal operations for creating and managing DuckDB snapshots
1.3 Restoring from Snapshots
When data issues occur, you can restore to a specific point in time:
-- Method 1: Query historical data using AS OF syntax
SELECT * FROM orders AS OF VERSION 42
WHERE created > '2026-08-27';
-- Method 2: Restore an entire table from a snapshot
CREATE TABLE orders_restored AS
SELECT * FROM orders AS OF SNAP 'snap_20260828';
-- Method 3: Attach snapshot as a read-only database
ATTACH 'snap_20260828.duckdb' AS snap_db (READ_ONLY);
SELECT * FROM snap_db.orders WHERE amount > 1000;
1.4 Snapshot Strategy Recommendations
| Scenario | Frequency | Retention |
|---|---|---|
| Dev/Test environment | Daily | Last 7 days |
| Production (small-scale) | Every 6 hours | Last 30 days |
| Production (large-scale) | Weekly full + daily incremental | Last 90 days |
-- SQL script for automatic snapshot cleanup
SELECT duckdb_snapshot('snap_' || current_date::TEXT);
-- Create new snapshot before ETL, delete old one after
DROP SNAPSHOT IF EXISTS snap_oldest;
2. EXPORT DATABASE: Structured Full Backup
2.1 Why EXPORT DATABASE?
While snapshots are lightweight, they have limitations:
- Snapshot files are tightly coupled to DuckDB format — not portable across engines
- Snapshots consume more space over time (despite CoW, accumulated modifications grow the size)
- Not suitable for long-term archival or offsite backup
EXPORT DATABASE exports the entire database as standardized SQL scripts or columnar format files, solving these issues.
2.2 Export as SQL Script
-- Export as human-readable SQL script (ideal for version control and review)
EXPORT DATABASE '/backup/ecommerce_sql' (FORMAT CSV);
-- Check the generated files
$ ls -lh /backup/ecommerce_sql/
total 1.2G
-rw-r--r-- 1 user user 45K orders.sql -- Schema + INSERT statements
-rw-r--r-- 1 user user 890M users.csv -- User table data
-rw-r--r-- 1 user user 230M products.csv -- Product table data
2.3 Export as Parquet (Recommended for Analytics)
-- Export as Parquet columnar format (high compression, ideal for analytics)
EXPORT DATABASE '/backup/ecommerce_parquet' (FORMAT PARQUET);
$ ls -lh /backup/ecommerce_parquet/
total 160M -- ~60% space savings compared to the original DuckDB file!
-rw-r--r-- 1 user user 128M orders.parquet
-rw-r--r-- 1 user user 30M users.parquet
-rw-r--r-- 1 user user 5M products.parquet
2.4 Restoring from Exported Files
-- Restore from SQL script
$ duckdb restored_db.duckdb
DuckDB> \i /backup/ecommerce_sql/orders.sql;
-- Restore from Parquet (create tables first)
DuckDB> CREATE TABLE orders AS SELECT * FROM read_parquet(['/backup/ecommerce_parquet/orders.parquet']);
DuckDB> CREATE TABLE users AS SELECT * FROM read_parquet(['/backup/ecommerce_parquet/users.parquet']);
DuckDB> CREATE TABLE products AS SELECT * FROM read_parquet(['/backup/ecommerce_parquet/products.parquet']);
3. Compressed Archives and Object Storage Integration
3.1 Exporting to S3/GCS Object Storage
For production environments, storing backups in object storage is best practice. DuckDB’s built-in httpfs extension supports direct export to cloud storage:
-- Configure AWS S3
INSTALL s3;
LOAD s3;
SET s3_region='us-east-1';
SET s3_access_key_id='YOUR_ACCESS_KEY';
SET s3_secret_access_key='YOUR_SECRET_KEY';
-- Export directly to S3
EXPORT DATABASE 's3://my-bucket/backups/ecommerce_20260828'
(FORMAT PARQUET);
-- Export to Google Cloud Storage
EXPORT DATABASE 'gs://my-bucket/backups/ecommerce_20260828'
(FORMAT PARQUET);
3.2 Automated Backup Script
#!/bin/bash
# automated_backup.sh — Daily backup script
DB_PATH="/data/ecommerce.duckdb"
BACKUP_DIR="/backup/daily"
DATE=$(date +%Y%m%d)
SNAP_NAME="snap_${DATE}"
# 1. Create snapshot
duckdb "$DB_PATH" -c "CREATE SNAPSHOT '$SNAP_NAME';"
# 2. Export Parquet to backup directory
duckdb "$DB_PATH" -c "EXPORT DATABASE '$BACKUP_DIR/ecommerce_$DATE' (FORMAT PARQUET);"
# 3. Compress backup files
tar czf "$BACKUP_DIR/ecommerce_$DATE.tar.gz" "$BACKUP_DIR/ecommerce_$DATE/"
rm -rf "$BACKUP_DIR/ecommerce_$DATE"
# 4. Upload to S3
aws s3 cp "$BACKUP_DIR/ecommerce_$DATE.tar.gz" \
"s3://my-bucket/backups/$DATE/"
# 5. Clean up backups older than 30 days
find "$BACKUP_DIR" -name "*.tar.gz" -mtime +30 -delete
find "$DB_PATH.snapshot" -mtime +30 -delete
echo "Backup completed: $DATE"
3.3 Backup Verification and Disaster Recovery Drills
The ultimate purpose of backup is successful recovery when needed. Regular recovery drills are recommended:
-- Verify backup integrity
-- 1. Check row counts match between source and backup
SELECT count(*) FROM read_parquet('/backup/ecommerce_parquet/orders.parquet');
-- Compare with source: SELECT count(*) FROM orders;
-- 2. Check data checksums
SELECT md5(array_agg(order_id::TEXT)) FROM orders;
SELECT md5(array_agg(order_id::TEXT))
FROM read_parquet('/backup/ecommerce_parquet/orders.parquet');
-- 3. Spot-check critical records for completeness and consistency
SELECT * FROM orders AS OF SNAP 'snap_20260828'
WHERE order_id IN (10001, 10002, 10003);
4. Cross-Version Upgrade Strategy
4.1 Smooth Upgrade Process
DuckDB maintains strong backward compatibility, but cross-major-version upgrades require caution:
# Before upgrade: create full backup
duckdb ecommerce.duckdb -c "EXPORT DATABASE '/backup/pre_upgrade' (FORMAT PARQUET);"
# Upgrade DuckDB CLI
# macOS: brew upgrade duckdb
# Linux: wget https://github.com/duckdb/duckdb/releases/download/v1.2.0/duckdb_cli-linux-amd64.zip
# After upgrade: verify data integrity
duckdb ecommerce.duckdb -c "SELECT * FROM duckdb_tables();"
duckdb ecommerce.duckdb -c "SELECT count(*) FROM orders;"
# If issues arise, restore from Parquet backup
duckdb new_db.duckdb -c "CREATE TABLE orders AS SELECT * FROM read_parquet('/backup/pre_upgrade/orders.parquet');"
4.2 Version Compatibility Matrix
| Source Version | Target Version | Compatibility | Notes |
|---|---|---|---|
| 0.8.x | 0.10.x | ✅ Fully compatible | Direct upgrade works |
| 0.10.x | 1.x | ✅ Compatible | Recommend Parquet export backup first |
| 1.x | 1.x+ | ✅ Fully compatible | Supports in-place upgrade |
5. Production Backup Strategy Summary
Combining all three strategies, here’s the recommended production-grade backup plan:
| Time Granularity | Strategy | Storage Location | Retention |
|---|---|---|---|
| Hourly | Snapshot | Local disk | 7 days |
| Daily | EXPORT DATABASE (Parquet) | Local + S3 | 30 days |
| Weekly | Compressed archive (.tar.gz) | S3 Glacier | 90 days |
| Monthly | Full export (SQL + Parquet) | Offsite backup | Permanent |
-- One-click full backup workflow
BEGIN;
CREATE SNAPSHOT snap_$(date +%Y%m%d);
EXPORT DATABASE 's3://bucket/daily/$(date +%Y%m%d)' (FORMAT PARQUET);
COMMIT;
Conclusion
DuckDB’s backup and migration may require manual design, but this also gives developers great flexibility. By combining snapshots for quick recovery, EXPORT DATABASE for standardized exports, and compressed archives for efficient storage, you can build a comprehensive production-grade data protection system.
For more DuckDB in Action tips, follow DuckDB Lab (duckdblab.org).