Skip to main content

Copying MotherDuck and DuckDB Databases

The COPY FROM DATABASE statement creates an exact duplicate of an existing database, including both schema and data. This functionality enables the following operations:

Interact with MotherDuck Databases

  • Copy between MotherDuck databases

Interact with Local Databases

  • Import local database to MotherDuck
  • Export MotherDuck database to local filesystem
  • Copy between local databases

The COPY FROM DATABASE command is implemented as a multiple statement macro, which is not supported in WebAssembly. As a result, simultaneous schema and data copying is not available in the MotherDuck Web UI. However, the Web UI supports copying schema only (SCHEMA option) or data only (DATA option). All functionality is available in other drivers, including the DuckDB CLI.

No zero-copy clone

COPY FROM DATABASE creates a physical copy of both the schema and the data. It does not use MotherDuck's zero-copy cloning, so the operation may take longer to run and will consume additional storage proportional to the size of the source database.

Syntax​

The syntax for COPY FROM DATABASE is:

COPY FROM DATABASE <source_database> TO <target_database> [ (SCHEMA) | (DATA) ]

Parameters​

  • <source_database>: The name or path of the source database to copy from
  • <target_database>: The name or path of the target database to create
  • (SCHEMA): Optional parameter to copy only the database schema without data
  • (DATA): Optional parameter to copy only the database data without schema

Example Usage​

Copy a MotherDuck database to a MotherDuck database​

This is the same as creating a new database from an existing one.

COPY FROM DATABASE my_db TO my_db_copy;

Interacting with Local Databases​

These operations can be done with access to the local filesystem, i.e. inside the DuckDB CLI.

Copy a local database to a MotherDuck database​

ATTACH 'md:';

COPY FROM DATABASE local_database.db TO md:md_database;

Copy a MotherDuck database to a local database​

To copy a MotherDuck database to a local database requires some extra steps.

ATTACH 'md:';

ATTACH 'local_database.db' as local_db;

COPY FROM DATABASE my_db TO local_db;

Copy a local database to a local database​

To copy a local database to a local database, please see the DuckDB documentation.

Copying the Database Schema​

COPY FROM DATABASE my_db TO my_db_copy (SCHEMA);

This will copy the schema of the database, but not the data.

Copying the Database Data​

COPY FROM DATABASE my_db TO my_db_copy (DATA);

This will copy the data of the database, but not the schema.