---
sidebar_position: 1
title: ALTER DATABASE SET SNAPSHOT
description: Restore a database from a snapshot using ALTER DATABASE SET SNAPSHOT TO.
---

## 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](/concepts/snapshots).

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

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

| Option | Type | Description |
|--------|------|-------------|
| SNAPSHOT_ID | UUID | Restores to the snapshot with this ID. The source database is inferred unless `DATABASE_NAME` is provided. |
| SNAPSHOT_TIME | TIMESTAMP | Restores to the newest snapshot created at or before this timestamp. Uses the target database unless `DATABASE_NAME` is provided. |
| SNAPSHOT_NAME | STRING | Restores to a named snapshot. Only valid for snapshots created with `CREATE SNAPSHOT <name> ...`. |
| DATABASE_NAME | STRING | Source 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`](/sql-reference/motherduck-sql-reference/alter-database).
- To list snapshots and their IDs, query [`MD_INFORMATION_SCHEMA.DATABASE_SNAPSHOTS`](/sql-reference/motherduck-sql-reference/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):

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

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

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

Restore to a snapshot by time from another database:

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

Restore to a named snapshot:

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