Skip to main content

ALTER DATABASE SET SNAPSHOT

Overview​

ALTER DATABASE ... SET SNAPSHOT TO overwrites a target database with the contents of a selected snapshot. You can restore from a snapshot created by the same database or from another database you own. For background on snapshots and retention, see the snapshots guide.

caution

This replaces the current contents of the target database. If you want to inspect a snapshot before overwriting, use CREATE DATABASE ... FROM ... to clone it first.

Syntax​

ALTER DATABASE <target_database> SET SNAPSHOT TO (
    SNAPSHOT_ID '<snapshot_id>' [, DATABASE_NAME '<source_database>']
  | SNAPSHOT_TIME '<timestamp>' [, DATABASE_NAME '<source_database>']
  | SNAPSHOT_NAME '<snapshot_name>'
);

Options​

OptionTypeDescription
SNAPSHOT_IDUUIDRestores to the snapshot with this ID. The source database is inferred unless DATABASE_NAME is provided.
SNAPSHOT_TIMETIMESTAMPRestores to the newest snapshot created at or before this timestamp. Uses the target database unless DATABASE_NAME is provided.
SNAPSHOT_NAMESTRINGRestores to a named snapshot. Only valid for snapshots created with CREATE SNAPSHOT <name> ....
DATABASE_NAMESTRINGSource database for SNAPSHOT_ID or SNAPSHOT_TIME. Not allowed with SNAPSHOT_NAME.

Notes​

  • Only one snapshot selector can be used per statement.
  • DATABASE_NAME is only valid with SNAPSHOT_ID or SNAPSHOT_TIME. It is not allowed with SNAPSHOT_NAME.
  • SNAPSHOT_TIME picks the newest snapshot created at or before the timestamp. Use UTC; the recommended format is YYYY-MM-DD HH:MM:SS[.ffffff].
  • Automatic and unnamed snapshots are only available if snapshot_retention_days is greater than 0. Named snapshots are retained until they are unnamed. See ALTER DATABASE.
  • To list snapshots and their IDs, query MD_INFORMATION_SCHEMA.DATABASE_SNAPSHOTS.
  • This statement applies to MotherDuck native storage databases. DuckLake databases do not support snapshot restore.

Examples​

Restore to a snapshot ID (source database inferred):

ALTER DATABASE my_db SET SNAPSHOT TO (SNAPSHOT_ID 'c204ce3b-f3fd-4677-8a05-e8680648cf27');

Restore to a snapshot ID from a specific source database (extra safety):

ALTER DATABASE my_db SET SNAPSHOT TO (
    DATABASE_NAME 'prod_db',
    SNAPSHOT_ID 'c204ce3b-f3fd-4677-8a05-e8680648cf27'
);

Restore to a snapshot by time from the same database:

ALTER DATABASE my_db SET SNAPSHOT TO (SNAPSHOT_TIME '2025-07-29 14:30:25');

Restore to a snapshot by time from another database:

ALTER DATABASE my_db SET SNAPSHOT TO (
    DATABASE_NAME 'prod_db',
    SNAPSHOT_TIME '2025-07-29 14:30:25'
);

Restore to a named snapshot:

ALTER DATABASE my_db SET SNAPSHOT TO (SNAPSHOT_NAME 'prod_backup');