Database Snapshots

What are snapshots?

Snapshots capture the complete state of a database at a specific point in time. MotherDuck automatically creates snapshots in the background for attached databases, enabling data recovery features such as restore and undrop.

How long snapshots are retained is controlled by the database setting snapshot_retention_days:

• 0 days: No historical snapshots are accessible
• 1+ days: Snapshots created within the snapshot_retention_days window can be accessed and restored

Users can modify snapshot retention at any time using ALTER DATABASE. To see your database's current snapshot retention, use md_information_schema.databases and look for the historical_snapshot_retention field.

Snapshot retention days are inherited when cloning a database.

How are snapshots created?

Snapshots are created in two ways - they are either automatic or manual.

Automatic snapshots

When a database is attached, MotherDuck continuously creates snapshots in the background automatically. These snapshots are retained and stored as historical_bytes according to the database's snapshot_retention_days setting.

Manually created snapshots

Users can explicitly capture the current state of a database using:

CREATE SNAPSHOT OF <database_name>;

If no name is provided, the snapshot behaves like an automatic snapshot and is subject to the database's snapshot retention window.

To create a named snapshot, include a name:

CREATE SNAPSHOT <snapshot_name> OF <database_name>;

Lite starts in free-tier mode with no historical retention until usage limits are reached, after which Lite defaults apply.

Plan	Snapshot Retention Default	Configurable Retention Period	Named Snapshots	Point-in-Time Restore	Undrop Database
Lite (free-tier mode)	0 days	N/A	N/A	N/A	N/A
Lite (after limits)	1 day	1 day	No	Yes	Yes
Business	1 day	0–90 days	Yes	Yes	Yes

Named Snapshots

Named snapshots are a type of manual snapshot that are explicitly given a name at creation time by using the CREATE SNAPSHOT command.

Named snapshots behave differently from regular snapshots:

• They are not garbage-collected by snapshot retention
• They persist even if the source database they are associated with is deleted
• They can be referenced directly by name when restoring or cloning a database
• Snapshot names must be unique per user
• They can only be deleted after you remove the name

Named snapshots are intended for long-lived backups and are the recommended mechanism for durable recovery points.

Named snapshots can be used with the ALTER DATABASE SET SNAPSHOT command, as well as the CREATE DATABASE FROM command to specify the snapshot you want to use.

Naming rules and gotchas:

• Snapshot names must be 1–255 characters long
• Names are unique per user across all databases
• If a name includes special characters (such as . or /), wrap it in double quotes
• If you create two named snapshots in a row without any new writes, the second can fail because the latest snapshot already has a name

Renaming a named snapshot

Users can change the name of an existing named snapshot by running the ALTER SNAPSHOT command:

ALTER SNAPSHOT <original snapshot name>
SET snapshot_name = '<new snapshot name>'

Named snapshots are only available on the Business Plan.

Deleting (un-naming) a named snapshot

To remove a name from a snapshot, run the following command:

ALTER SNAPSHOT <original_snapshot_name>
SET snapshot_name = '';

Once unnamed, the snapshot will become subject to the database's snapshot_retention_days policy and will be deleted automatically when it falls outside the retention window.

Best practices

• Use named snapshots for long-lived backups you may need to restore far into the future
• Use short snapshot retention windows (1–7 days) to protect against accidental overwrites
• Failsafe restores should be thought of as a precautionary, last-minute measure in exception scenarios
• Do not use transient databases for critical or hard-to-reconstruct data