Skillversitas

Export and Backup

DuckDB offers two complementary ways to move data out of a database: COPY TO for a single table or query, and EXPORT DATABASE for an entire database.

COPY TO

COPY orders TO 'orders.csv' (HEADER, DELIMITER ',');
COPY orders TO 'orders.parquet' (FORMAT PARQUET);
COPY (SELECT * FROM orders WHERE year = 2026) TO 'orders_2026.json' (FORMAT JSON);

Supported formats include CSV, Parquet, JSON, and Arrow. Parquet is the recommended export format: columnar, compressed, and re-readable by DuckDB without loss.

EXPORT DATABASE

EXPORT DATABASE writes every table and view into a directory of Parquet files, one file per table. It is the fastest way to back up an entire database:

EXPORT DATABASE mydb TO 'backup_2026_11_06' (FORMAT PARQUET);

Restoring with ATTACH

A Parquet "database directory" can be attached like a regular database and read with plain SQL:

ATTACH 'backup_2026_11_06' AS backup (READ_ONLY);
CREATE TABLE orders AS SELECT * FROM backup.orders;
DETACH backup;

Backup Workflow

  1. Quiesce writes: stop the writer process or open the file read_only.
  2. Run EXPORT DATABASE into a timestamped directory.
  3. Copy the directory to cold storage (rsync, S3, tape).
  4. Verify by ATTACHing the backup and comparing row counts and checksums.

Summary

Use COPY TO to export individual tables or query results to CSV, Parquet, JSON, or Arrow, and EXPORT DATABASE to back up an entire database as a Parquet directory. Restore by ATTACHing the backup directory read-only and copying data back with SQL. Always test a restore on a schedule.