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
- 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.
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.