Livestream: Getting Started with MotherDuck and DuckLake - June 26Register Now

Skip to main content

COPY FROM DATABASE

The COPY FROM DATABASE statement creates a new database from an existing one by copying its structure and data. This command can be used to:

Interact with MotherDuck Databases

  • Copy MotherDuck databases to MotherDuck databases

Interact with Local Databases

  • Copy local databases to MotherDuck databases
  • Copy MotherDuck databases to local databases
  • Copy local databases to local databases
Driver Support

The COPY FROM DATABASE command is a multiple statement macro. Multiple statement macros are not supported in Wasm and as a result, this command will not work in the MotherDuck Web UI when copying both schema and data. However, the command works in the MotherDuck Web UI if either the (DATA) option is specified or the (SCHEMA) option is specified. All other drivers support this command, including the DuckDB CLI.

Syntax​

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.