Backup and Export

Learning Focus

Use this lesson to safely back up and export DuckDB data using multiple strategies.

Strategy 1: File Copy (Simplest)

DuckDB stores everything in a single .db file. The simplest backup is a file copy:

# Stop any active writes, then copy the file
cp production.db production_backup_$(date +%Y%m%d).db

caution

Only copy a DuckDB file when no active connection has it open for writing. Otherwise use EXPORT DATABASE or CHECKPOINT first.

Strategy 2: CHECKPOINT Before Backup

-- Flush WAL (write-ahead log) to main database file
CHECKPOINT;

After CHECKPOINT, the .db file is in a consistent state and can be safely copied.

duckdb production.db "CHECKPOINT;" && cp production.db backup.db

Strategy 3: EXPORT DATABASE (Portable Backup)

EXPORT DATABASE exports an entire database to SQL + Parquet files — portable across DuckDB versions:

-- Export all tables to a directory
EXPORT DATABASE '/backups/production_export/' (FORMAT PARQUET);

-- Or export as SQL + CSV
EXPORT DATABASE '/backups/production_export/' (FORMAT CSV);

Directory structure:

production_export/
├── load.sql        ← CREATE TABLE statements
├── employees.parquet
├── orders.parquet
└── products.parquet

Strategy 4: IMPORT DATABASE (Restore)

-- Restore from an EXPORT DATABASE directory
IMPORT DATABASE '/backups/production_export/';

Export Specific Tables

-- Export a single table to Parquet
COPY employees TO 'backup/employees.parquet' (FORMAT PARQUET);

-- Export with timestamp in filename
COPY employees TO 'backup/employees_20240615.parquet' (FORMAT PARQUET);

-- Export a filtered query result
COPY (SELECT * FROM orders WHERE order_date >= '2024-01-01')
TO 'backup/orders_2024.parquet' (FORMAT PARQUET, COMPRESSION ZSTD);

Automated Backup Script (Bash)

#!/bin/bash
DB_PATH="/data/production.db"
BACKUP_DIR="/backups/duckdb"
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p "$BACKUP_DIR"

# Checkpoint then copy
duckdb "$DB_PATH" "CHECKPOINT;"
cp "$DB_PATH" "$BACKUP_DIR/production_$DATE.db"

# Keep last 7 backups
ls -t "$BACKUP_DIR"/*.db | tail -n +8 | xargs rm -f

echo "Backup complete: production_$DATE.db"

Common Pitfalls

Pitfall	Consequence	Prevention
Copying .db file while in use	Corrupted or incomplete backup	Always CHECKPOINT before file copy
No version check on EXPORT/IMPORT	Schema incompatibility on older DuckDB	Use EXPORT DATABASE for cross-version portability
Skipping backups for "analytical-only" data	Data loss on disk failure	Even read-only DBs should be backed up

Quick Reference

CHECKPOINT;
EXPORT DATABASE '/path/export/' (FORMAT PARQUET);
IMPORT DATABASE '/path/export/';
COPY t TO 'backup.parquet' (FORMAT PARQUET, COMPRESSION ZSTD);

What's Next

• Next: DuckDB CLI Advanced - Power-user CLI workflows.
• Module Overview - Return to this module index.