Data Recovery

Overview

MotherDuck provides versioned 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 behavior apply. This page covers an example workflow with named snapshots and outlines how to restore a database to a historical snapshot within the snapshot retention window (snapshot_retention_days). Refer to the information on Snapshots for more details.

Plan	Snapshot Retention Default	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

Snapshots can be used to restore a new database to the snapshot using CREATE DATABASE or to ALTER an existing database to reflect the contents of a specific snapshot.

Automatic snapshots are retained for a set period of time in line with the specified plan defaults after they are no longer the active snapshot for a database.
Named snapshots are created explicitly and persist until unnamed and they are not garbage-collected by snapshot retention.

A new database:

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

An existing database:

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

Snapshots can also be used to recover a dropped database:

UNDROP DATABASE <db_name>

Refer to undrop database page for details.

Example:

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

UNDROP DATABASE recovery_demo;

Refer to the named snapshots page for an example.

Restoring Your Database to a Named Snapshot

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:

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,

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

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.

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

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'

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

However, if you run:

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.

Overview​

Restoring Your Database to a Named Snapshot​

Restoring a Database to a Historical Snapshot​

Overview

Restoring Your Database to a Named Snapshot

Restoring a Database to a Historical Snapshot