---
title: Data Recovery
sidebar_position: 4
description: 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. On the Lite plan, databases only keep the active snapshot (no historical retention) until usage limits are reached, after which Lite snapshot retention and [`UNDROP`](/sql-reference/motherduck-sql-reference/undrop-database) behavior apply. 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.

### Snapshot options per plan

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

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