Skip to main content

CREATE SNAPSHOT

The CREATE SNAPSHOT statement creates a snapshot of a single MotherDuck database. You can create an unnamed snapshot or provide a name to create a named snapshot.

Named snapshots can be looked up and restored later. Their names can be updated (or removed) using ALTER SNAPSHOT. For retention and recovery behavior, see the data recovery guide.

Syntax​

CREATE SNAPSHOT [<snapshot_name>] OF <database_name>;

Notes​

  • Only one database can be snapshotted per statement.
  • If you omit a name, the snapshot is eligible for restore only while it remains within the database's SNAPSHOT_RETENTION_DAYS window. Use ALTER DATABASE to configure retention.
  • If you provide a name, the snapshot becomes a named snapshot and is retained until it is unnamed.
  • CREATE SNAPSHOT waits for active write queries to finish and blocks new writes until the snapshot is created.
  • MotherDuck also takes automatic snapshots in the background every minute when no write queries are running.

Read scaling​

Creating a snapshot will make the latest data available to read-scaling connections.

Each read-scaling instance picks up the latest available snapshot every minute. To minimize delays and ensure access to the latest data, use CREATE SNAPSHOT on the writer connection, followed by a REFRESH DATABASE <name> on the read scaling connection.

Learn more about REFRESH DATABASES.

Examples​

Create a named snapshot:

CREATE SNAPSHOT 'prod_backup' OF my_db;

Create an unnamed snapshot:

CREATE SNAPSHOT OF my_db;