CREATE DATABASE

The CREATE DATABASE statement creates a new MotherDuck database. You can also use it to:

• 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, 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

For native storage-backed databases, MotherDuck supports two ways for users to configure historical retention periods. At database creation, users can either set the database type as transient or standard (default). Each database type comes with its own failsafe period.

Name	Data Type	Value
STANDARD	native storage format	Leave blank; any database created in MotherDuck will default to a standard, native storage-backed database.
TRANSIENT	native storage format	Specify TRANSIENT at database creation to enable it to use transient storage. Refer to Storage Lifecycle Management for more details.
DUCKLAKE	integrated data lake and catalog format	Specify TYPE DUCKLAKE at database creation to create a fully managed DuckLake. Refer to the DuckLake Overview for more details.

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:
CREATE OR REPLACE DATABASE ducks FROM ducks_db;

To configure database options in MotherDuck:

CREATE DATABASE cloud_db (TRANSIENT)

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

CREATE DATABASE cloud_db FROM another_cloud_db;

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;