Database Snapshots

What are snapshots?

Snapshots capture the complete state of a database at a specific point in time. MotherDuck creates historical snapshots in the background for attached databases (databases that are connected to MotherDuck and available for querying), enabling data recovery features such as restore and undrop. Historical snapshots come in two forms.

1. Automatic snapshots

Automatic snapshots are created continuously in the background by MotherDuck whenever data changes. For paid plans every new database has automatic snapshots configured by default. For paid plans you can also set or adjust your database's snapshot retention window with:

ALTER DATABASE example_database SET SNAPSHOT_RETENTION_DAYS = 4;

Automatic snapshots:

• Are created whenever data in the database changes or explicitly with CREATE SNAPSHOT OF <database_name>;
• Are retained as historical_bytes according to the database's snapshot_retention_days setting
• Can be queried using md_information_schema.database_snapshots
• Are automatically removed by garbage collection when they fall outside the retention window

2. Named snapshots

Named snapshots have to be explicitly created with a name using CREATE SNAPSHOT.

CREATE SNAPSHOT my_backup OF example_database

These persist indefinitely until the name is removed. Named snapshots are not subject to automatic garbage collection and are only available on the Business plan.

Named snapshots differ from automatic 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 by removing the name, after which they are picked up by garbage collection

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.

Restoring a database

You can restore a database from a snapshot by specifying the snapshot name, snapshot_id or a timestamp. When using a timestamp the latest snapshot at or before that time will be selected.

CREATE DATABASE example_db_from_snap FROM example_db (SNAPSHOT_NAME 'snap');

CREATE DATABASE example_db_from_id FROM example_db (SNAPSHOT_ID '4bfbd992-e586-48ab-9176-8dfb2d2c30b4');

CREATE DATABASE example_db_from_ts FROM example_db (SNAPSHOT_TIME '2026-01-01 00:00:01.234567');

Snapshot features per plan

Plan	Automatic Snapshot Retention Default	Automatic Snapshot Configurable Retention Period	Named Snapshots	Point-in-Time Restore	UNDROP Database
Business	7 days	0–90 days	Yes	Yes	Yes
Lite (paid)	1 day	1 day	No	Yes	Yes
Lite (free)	0 days	N/A	N/A	N/A	N/A

Snapshot retention

The snapshot_retention_days database setting controls how long historical snapshots are retained for data recovery. This setting determines how much data is stored as historical_bytes in your storage footprint.

• 0 days: No historical snapshots are accessible; automatic snapshots are immediately eligible for garbage collection
• 1+ days: Automatic snapshots created within the retention window can be accessed and restored

Users can modify snapshot retention at any time using ALTER DATABASE:

ALTER DATABASE my_database SET SNAPSHOT_RETENTION_DAYS = 4;

To see your database's current snapshot retention, use md_information_schema.databases and look for the historical_snapshot_retention field.

note

Snapshot retention days are inherited when cloning a database.

important

Increasing snapshot_retention_days does not restore previously deleted snapshots. Once the garbage collection process removes a snapshot, it cannot be recovered through this setting.

Working with named snapshots

Named snapshots are subject to naming rules.

• 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 using the ALTER SNAPSHOT command:

ALTER SNAPSHOT <original_snapshot_name> SET snapshot_name = '<new_snapshot_name>';

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.

Historical snapshots and failsafe bytes

It's important to understand the distinction between historical snapshots and failsafe data:

• Historical snapshots are point-in-time copies of your database that you can restore yourself using SQL commands. They are stored as historical_bytes and controlled by your snapshot_retention_days setting.
• Failsafe data is a system-managed backup that MotherDuck retains for disaster recovery. It is stored as failsafe_bytes and can only be restored by contacting MotherDuck support.

	Historical Snapshots: historical_bytes	Failsafe Data: failsafe_bytes
Purpose	User-initiated data recovery and point-in-time restore	System-level disaster recovery backup
Controlled by	snapshot_retention_days setting	MotherDuck system (7 days for standard databases, 1 day for transient)
Recovery method	Self-service via ALTER DATABASE SET SNAPSHOT or CREATE DATABASE FROM	Requires contacting MotherDuck support
Visibility	Queryable via md_information_schema.database_snapshots	Not directly visible to users
Storage billing	Billed as historical_bytes	Billed as failsafe_bytes

For more details on storage lifecycle stages, see Storage Lifecycle and Management.

Best practices

• Use named snapshots for long-lived backups you may need to restore far into the future
• If you frequently overwrite your data, use a short snapshot retention window (1-7 days) to avoid storing multiple copies of the same data
• Failsafe restores should be thought of as a precautionary, last-minute measure in exception scenarios only; we recommend using historical snapshots for routine recovery needs
• Do not use transient databases for critical or hard-to-reconstruct data

Related Content

• Data Recovery — Step-by-step guide to restoring databases from snapshots
• Storage Lifecycle and Management — Understanding storage stages and billing
• CREATE SNAPSHOT — SQL reference for creating snapshots
• DATABASE_SNAPSHOTS view — Query snapshot history and metadata
• CREATE DATABASE from a snapshot