CREATE DATABASE

The CREATE DATABASE statement creates a new database in MotherDuck.

It can be used for the following operations:

Create a MotherDuck database from a local DuckDB database.
Create a MotherDuck database from another MotherDuck database or share via zero-copy clone (without physically copying data).

Copy to local database

To copy a MotherDuck database to a local database, use the COPY FROM DATABASE statement.

Zero-copy clone

When the source is another MotherDuck database or a share, CREATE DATABASE ... FROM performs a zero-copy clone. The command completes almost instantly because no data is physically duplicated. When the source is a local file or CURRENT_DATABASE(), data is physically copied to MotherDuck.

Syntax

CREATE [ OR REPLACE ] DATABASE [ IF NOT EXISTS ] <database name>
[
    FROM <database name> |
    FROM '<local/file/path.db>' |
    FROM 'md:_share/...' |
    FROM CURRENT_DATABASE() -- Important: this command does not work with attached shares
]

[(DATABASE OPTIONS)];

You can also pass the name of an attached share or a share URL as the database name, for example CREATE DATABASE FROM my_share or CREATE DATABASE FROM 'md:_share/...'.

If the database name already exists, the statement returns an error unless you specify IF NOT EXISTS.

Similar to DuckDB table name conventions, database names that start with a number or contain special characters must be double-quoted when used. Example: CREATE DATABASE "123db"

Creating a database does not change the active database. Run USE DATABASE <database name> to switch.

Database Options

Databases on MotherDuck are either Native Storage backed databases, or DuckLake databases. Each type has certain options which can be configured upon creation.

All Native Storage backed databases have a transient status and a historical retention period.

MotherDuck supports configuring historical retention periods upon creation, as well as after creation with ALTER DATABASE.

Users can set the transient status upon creation. This can't be changed after database creation. Transient databases have a different failsafe period than non-transient databases.

Name	Used for Database Type	Value
STANDARD	Native Storage	Leave blank; any database created in MotherDuck will default to a standard, native storage-backed database.
TRANSIENT	Native Storage	Specify `TRANSIENT` at database creation to enable it to use transient storage. Refer to the Storage Lifecycle Management overview for more details.
SNAPSHOT_RETENTION_DAYS	Native Storage	Provide an integer to specify the number of days to retain automatic and unnamed snapshots as `historical_bytes`. Named snapshots are retained until unnamed. Refer to the Storage Lifecycle Management overview for more details.
DUCKLAKE	DuckLake	Specify `TYPE DUCKLAKE` at database creation to create a fully managed DuckLake. Refer to the DuckLake Overview for more details.
DATA_PATH	DuckLake	Optional data path for DuckLake storage (for example, `DATA_PATH 's3://bucket/prefix'`). Buckets must be in the same region as your MotherDuck org.
ENCRYPTED	DuckLake	Enables encryption for DuckLake storage. Use `ENCRYPTED true` or `ENCRYPTED false`.
DATA_INLINING_ROW_LIMIT	DuckLake	Row-size threshold (bytes) for inline data storage. Provide an integer value.

Source Database Options

These options are only available for native MotherDuck databases. They apply to the source database that is being cloned.

Snapshot selectors are only supported when cloning a native MotherDuck database. They are not supported for DuckLake databases.

Name	Data Type	Value
SNAPSHOT_TIME	TIMESTAMP	Selects the newest snapshot created before or at this timestamp
SNAPSHOT_ID	UUID	ID of the snapshot to clone
SNAPSHOT_NAME	STRING	Name of the snapshot to clone

Once these properties are set, they cannot be changed. However, MotherDuck supports cloning databases and copying data content between transient and standard databases. Note that the following syntax CREATE DATABASE empty_duck FROM non_empty_duck (TRANSIENT); is not supported. Please refer to the examples below for supported methods.

Example Usage

To create an empty database:

CREATE DATABASE empty_ducks;

If the database name already exists, the statement fails unless you use OR REPLACE or IF NOT EXISTS.

CREATE DATABASE ducks;
-- Succeeds if 'ducks' does not exist

CREATE DATABASE ducks;
-- Error: Failed to create database: database with name 'ducks' already exists

CREATE OR REPLACE DATABASE ducks; -- Replaces existing 'ducks' with an empty database

CREATE DATABASE IF NOT EXISTS ducks; -- No-op if 'ducks' already exists

To copy an entire database from your local DuckDB instance into MotherDuck:

USE ducks_db;
CREATE DATABASE ducks FROM CURRENT_DATABASE();

-- Or alternatively, use the following command - if ducks_db exists, even if populated, it will be replaced with an empty one:
CREATE OR REPLACE DATABASE ducks FROM ducks_db;

-- In the following, if ducks_db exists, the operation will be skipped, but it will not error:
CREATE DATABASE IF NOT EXISTS ducks_db;

To configure database options in MotherDuck:

-- Create a transient database:
CREATE DATABASE cloud_db (TRANSIENT);

-- Create a database with seven days retention:
CREATE DATABASE cloud_db (SNAPSHOT_RETENTION_DAYS 7)

-- Create a DuckLake:
CREATE DATABASE cloud_ducklake (TYPE DUCKLAKE);

-- Create a DuckLake with a storage path and encryption:
CREATE DATABASE cloud_ducklake
(
    TYPE DUCKLAKE,
    DATA_PATH 's3://my-bucket/ducklake',
    ENCRYPTED true
);

To copy content between standard and transient databases:

-- Option 1: Clone with inherited properties
-- The new database inherits the transient/standard property from the source
CREATE OR REPLACE DATABASE dest_db FROM source_db;

-- Option 2: Copy content while preserving destination properties
-- Replaces the contents of dest_db without changing its configuration
CREATE DATABASE dest_db (TRANSIENT);
COPY FROM DATABASE source_db (OVERWRITE) TO dest_db;

Limitations

You cannot copy data from a transient database into a standard database using COPY FROM DATABASE (OVERWRITE), as this would violate the standard database's 7-day failsafe guarantee. To copy transient data into a standard database, use Option 1.

To zero-copy clone a database that is already attached in MotherDuck:

CREATE DATABASE cloud_db FROM another_cloud_db;

To zero-copy clone a past snapshot of a database in MotherDuck

CREATE DATABASE cloud_db FROM another_cloud_db (SNAPSHOT_NAME 'prod_backup');
CREATE DATABASE cloud_db FROM another_cloud_db (SNAPSHOT_ID '3f2504e0-4f89-11d3-9a0c-0305e82c3301');
CREATE DATABASE cloud_db FROM another_cloud_db (SNAPSHOT_TIME '2025-07-29 14:30:25.123456');

To upload a local DuckDB database file:

CREATE DATABASE flying_ducks FROM './databases/local_ducks.db';

To upload an attached local DuckDB database:

ATTACH './databases/local_ducks.db';
CREATE DATABASE flying_ducks FROM local_ducks;

Syntax​

Database Options

Source Database Options

Example Usage​

Syntax

Example Usage