---
sidebar_position: 1
title: CREATE DATABASE
description: Create a database, zero-copy clone from an existing database, or import a local DuckDB file into MotherDuck.
---

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](/key-tasks/sharing-data/sharing-overview/) using zero-copy clone (without physically copying data).

:::note Copy to local database
To copy a MotherDuck database to a local database, use the [`COPY FROM DATABASE`](/sql-reference/motherduck-sql-reference/copy-database.md) statement.
:::

::::tip 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

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

MotherDuck supports configuring historical retention periods upon creation, as well as after creation with [`ALTER DATABASE`](/sql-reference/motherduck-sql-reference/alter-database).

You 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      | 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](/concepts/storage-lifecycle#transient-databases) 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](/concepts/storage-lifecycle#storage-management) for more details. |
| DUCKLAKE   | DuckLake | Specify `TYPE DUCKLAKE` at database creation to create a fully managed DuckLake. Refer to the [DuckLake overview](/integrations/file-formats/ducklake/) 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` for US, `eu-central-1` for EU). |
| ENCRYPTED | DuckLake | Enables encryption for DuckLake storage. To enable it, specify `ENCRYPTED` at database creation. Refer to [Encryption](https://ducklake.select/docs/stable/duckdb/advanced_features/encryption) for more details. |
| DATA_INLINING_ROW_LIMIT | DuckLake | Row-size threshold (bytes) for inline data storage. Provide an integer value. |
| AUTO_MAINTENANCE | DuckLake | Enables or disables [auto maintenance](/concepts/ducklake#auto-maintenance). Fully managed DuckLake databases have this enabled by default; BYOB databases have it disabled by default. |
| SNAPSHOT_RETENTION_DAYS | DuckLake | Number of days to retain DuckLake snapshots. Defaults to `NULL` (infinite retention). Requires `AUTO_MAINTENANCE` to be enabled. |

## 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:

```sql
CREATE DATABASE empty_ducks;
```

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

```sql
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:

```sql
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:

```sql
-- 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 auto maintenance and snapshot retention:
CREATE DATABASE my_ducklake
(
    TYPE DUCKLAKE,
    AUTO_MAINTENANCE TRUE,
    SNAPSHOT_RETENTION_DAYS 7
);
```

To copy content between standard and transient databases:

```sql
-- 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;
```

:::note 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:

```sql
CREATE DATABASE cloud_db FROM another_cloud_db;
```

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

```sql
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:

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

To upload an attached local DuckDB database:

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