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 using 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 <snapshot_name> | <snapshot_id> | <snapshot_time>
    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 databases or DuckLake databases. Each type has certain options which can be configured upon creation.

All native storage databases have a transient status and a historical retention period. These properties are inherited on new databases created with the CREATE DATABASE dest_db FROM source_db syntax.

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

You can set the transient status when creating a database, but it can't be altered after. Transient databases have a different failsafe period than non-transient databases.

Name	Database type	Description
STANDARD	Native storage	Leave blank; any database created in MotherDuck defaults to a standard, native storage database.
TRANSIENT	Native storage	Specify TRANSIENT at database creation to enable 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 AWS region as your MotherDuck org (us-east-1 or us-west-2 for US, eu-central-1 or eu-west-1 for EU).
ENCRYPTED	DuckLake	Enables encryption for DuckLake storage. To enable it, specify ENCRYPTED at database creation. Refer to Encryption for more details.
DATA_INLINING_ROW_LIMIT	DuckLake	Row-size threshold (bytes) for inline data storage. Provide an integer value.
SNAPSHOT_RETENTION_DAYS	DuckLake	Number of days to retain DuckLake snapshots before they are eligible for expiration. Defaults to NULL (infinite retention). DuckLake snapshots are expired by running maintenance operations manually; MotherDuck does not expire them automatically.

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

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
);

-- Create a DuckLake with a snapshot retention period:
CREATE DATABASE my_ducklake
(
    TYPE DUCKLAKE,
    SNAPSHOT_RETENTION_DAYS 7
);

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;