# Data Recovery
> Understand MotherDuck's data recovery mechanisms
## Overview

MotherDuck provides [historical snapshots](/concepts/snapshots) to support point-in-time backup/restore mechanisms on Lite and Business plans. Lite organizations start in free-tier mode, where databases keep only the active snapshot (no historical retention). After the organization switches to Lite (paid), newly created databases get 1 day of retention and [`UNDROP`](/sql-reference/motherduck-sql-reference/undrop-database) becomes available for them, while databases created in free-tier mode keep 0-day retention. Retention on Lite is fixed by the plan; only Business plan users can change `snapshot_retention_days`. This page covers an example workflow with [named snapshots](/concepts/snapshots#2-named-snapshots) and outlines how to restore a database to a historical snapshot within the [snapshot retention](/concepts/snapshots#snapshot-retention) window (`snapshot_retention_days`). Refer to the [Database Snapshots](/concepts/snapshots) page for more details.

:::note[DuckLake databases]
DuckLake databases manage snapshots through [DuckLake maintenance operations](/concepts/ducklake#maintenance), which you run manually. Snapshot retention for DuckLake defaults to infinite (`NULL`) and is configured with `SNAPSHOT_RETENTION_DAYS` through [`ALTER DATABASE`](/sql-reference/motherduck-sql-reference/alter-database). See [DuckLake snapshot retention](/concepts/ducklake#snapshot-retention) for details.
:::

### Snapshot options per plan (native storage)

| Plan | Snapshot Retention Default | Configurable Retention Period | Named Snapshots | Point-in-Time Restore | [`UNDROP`](/sql-reference/motherduck-sql-reference/undrop-database) Database |
|------|----------------------------|-------------------------------|-----------------|----------------------|-------------------|
| **Business** | 7 days | Yes, 0-90 days | Yes | Yes | Yes |
| **Lite (paid)** | 1 day | No, fixed at 1 day | No | Yes | Yes |
| **Lite (free)** | 0 days | No, fixed at 0 days | No | No | No |

Snapshots can be used to restore a new database to the snapshot using [`CREATE DATABASE`](/sql-reference/motherduck-sql-reference/create-database) or to [`ALTER`](/sql-reference/motherduck-sql-reference/alter-database-snapshot) an existing database to reflect the contents of a specific snapshot.

- **[Automatic snapshots](/concepts/snapshots#1-automatic-snapshots)** are retained for a set period of time according to `snapshot_retention_days` after they are no longer the active snapshot for a database.
- **[Named snapshots](/concepts/snapshots#2-named-snapshots)** are created explicitly and persist until unnamed. They are not subject to automatic garbage collection.

A new database:

```sql
CREATE DATABASE <new_db> FROM <current_db> (
    SNAPSHOT_TIME ... | SNAPSHOT_NAME ... | SNAPSHOT_ID ...
)
```

An existing database:

```sql
ALTER DATABASE <existing_db> SET SNAPSHOT TO (
    SNAPSHOT_TIME ... | SNAPSHOT_NAME ... | SNAPSHOT_ID ...
)
```

Snapshots can also be used to recover a dropped database:

```sql
UNDROP DATABASE <db_name>
```

Refer to [undrop database](/sql-reference/motherduck-sql-reference/undrop-database) page for details.

Example:

```sql
-- You cannot drop the currently active database
USE some_other_db;
DROP DATABASE recovery_demo;

UNDROP DATABASE recovery_demo;
```

Refer to the [named snapshots](/sql-reference/motherduck-sql-reference/create-snapshot) page for an example.

## Restoring your database to a named snapshot

```sql
CREATE DATABASE example_db;

USE example_db;
CREATE TABLE one AS SELECT  1;
CREATE SNAPSHOT one OF example_db;
CREATE TABLE two AS SELECT  2;
CREATE SNAPSHOT two OF example_db;
CREATE TABLE three AS SELECT 3;
CREATE SNAPSHOT three OF example_db;

-- Accidentally drop data!
DROP TABLE two;

-- Restore a previous snapshot of the DB and check it's what you want
CREATE DATABASE example_restore FROM example_db (SNAPSHOT_NAME 'three');

-- The snapshot looks correct!
SELECT * FROM example_restore.two;

-- Restore the database to the old valid snapshot
ALTER DATABASE example_db SET SNAPSHOT TO (SNAPSHOT_NAME 'three');

-- We have successfully restored our data!
SELECT * FROM two;
```

## Restoring a database to a historical snapshot

To find all snapshots corresponding to your database, run the following queries.

To see the history of snapshots for a given database:

```sql
SELECT * FROM MD_INFORMATION_SCHEMA.DATABASE_SNAPSHOTS WHERE database_name = '<your_database_name>' ORDER BY created_ts DESC;
```

If you have a rough idea of the time range you want to restore your database to, you can filter the above query by `created_ts`,

```sql
SELECT snapshot_id, created_ts, active_bytes FROM MD_INFORMATION_SCHEMA.DATABASE_SNAPSHOTS
WHERE database_name = '<your_database_name>'
and created_ts >= '2024-12-02 20:00:00'
and created_ts <= '2024-12-02 20:05:00'
ORDER BY created_ts DESC
```

The results should look something like this:

| snapshot_id | created_ts | active_bytes|
|-------------|-----------|----------|
| `73034f48-e832-40d6-a30f-9055eb302a2e` | `2024-12-02 20:03:30` | `2191330` |
| `c204ce3b-f3fd-4677-8a05-e8680648cf27` | `2024-12-02 20:02:05` | `2183991` |
| `63395025-b139-4c6f-8fc2-7b8c0feff748` | `2024-12-02 20:01:55` | `1847296` |

Example (restore an existing database by ID):

```sql
ALTER DATABASE your_database_name
SET SNAPSHOT TO (SNAPSHOT_ID '<snapshot_id_from_query>');
```

Both automated and named snapshots can be used to restore to a desired state that was captured. Users can either restore a new or existing database to a specific snapshot.

```sql
-- For a new database
CREATE DATABASE restored_database FROM your_database_name (SNAPSHOT_ID 'c204ce3b-f3fd-4677-8a05-e8680648cf27');
```

After running the above command, users can run queries on `restored_database` and use the state of the database from a prior point-in-time.

Once users have the exact snapshot they are interested in restoring, we recommend finding the `snapshot_id` (instead of using `snapshot_time`) and using the command:

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

**Note:** Running a `SET SNAPSHOT TO` command that specifies a timestamp that doesn't exist in `md_information_schema.database_snapshots` will select the most recent snapshot created at or before the specified timestamp. In our example, snapshot `63395025-b139-4c6f-8fc2-7b8c0feff748` would be selected because it is the only snapshot in the information schema table that was created before `'2024-12-02 20:02:04'`

```sql
ALTER DATABASE your_database_name SET SNAPSHOT TO (SNAPSHOT_TIME '2024-12-02 20:02:04');
```

However, if you run:

```sql
ALTER DATABASE your_database_name SET SNAPSHOT TO (SNAPSHOT_TIME '2024-12-02 20:02:05');
```

then snapshot `c204ce3b-f3fd-4677-8a05-e8680648cf27` will be selected because there is an exact timestamp match.

## See also

- [Database Snapshots](/concepts/snapshots) — Understanding snapshot types, retention, and best practices
- [`CREATE SNAPSHOT`](/sql-reference/motherduck-sql-reference/create-snapshot) — SQL reference for creating snapshots
- [`UNDROP DATABASE`](/sql-reference/motherduck-sql-reference/undrop-database) — Recovering dropped databases


---

## Docs feedback

MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`.
For agents and automated tools, feedback submission should be user-confirmed before sending.

URL-encode query parameter values and send a GET request:

```text
GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fconcepts%2Fdata-recovery%2F&page_title=Data%20Recovery&text=<url-encoded user feedback, max 2000 characters>
```

Optionally append `&source=<url-encoded interface identifier>` such as `claude.ai` or `chatgpt`.

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed query parameters, and `429` when rate-limited.