# Database Snapshots > Understand how snapshots work in MotherDuck, including retention, restore, snapshot management, and plan availability ## What are snapshots? Snapshots capture the complete state of a database at a specific point in time. MotherDuck creates **historical snapshots** in the background for attached databases (databases that are connected to MotherDuck and available for querying), enabling [data recovery](/concepts/data-recovery) features such as restore and [undrop](/sql-reference/motherduck-sql-reference/undrop-database). Historical snapshots come in two forms. :::note DuckLake databases DuckLake databases have their own snapshot and maintenance system. Snapshot lifecycle management for DuckLake is handled through [auto maintenance](/concepts/ducklake#auto-maintenance), not the native storage snapshot system described on this page. See [DuckLake snapshot retention](/concepts/ducklake#snapshot-retention) for details on configuring `SNAPSHOT_RETENTION_DAYS` and `AUTO_MAINTENANCE` for DuckLake databases. ::: ### 1. automatic snapshots {#1-automatic-snapshots} Automatic snapshots are created continuously in the background by MotherDuck whenever data changes. For paid plans every new database has automatic snapshots configured by default. For paid plans you can also set or adjust your database's snapshot retention window with: ```sql ALTER DATABASE example_database SET SNAPSHOT_RETENTION_DAYS = 4; ``` Automatic snapshots: - Are created whenever data in the database changes or explicitly with `CREATE SNAPSHOT OF ;` - Are retained as [`historical_bytes`](/concepts/storage-lifecycle) according to the database's `snapshot_retention_days` setting - Can be queried using [`md_information_schema.database_snapshots`](/sql-reference/motherduck-sql-reference/md_information_schema/database_snapshots) - Are automatically removed by garbage collection when they fall outside the retention window ### 2. named snapshots {#2-named-snapshots} Named snapshots have to be explicitly created with a name using [`CREATE SNAPSHOT`](/sql-reference/motherduck-sql-reference/create-snapshot). ```sql CREATE SNAPSHOT my_backup OF example_database ``` These persist indefinitely until the name is removed. Named snapshots are **not** subject to automatic garbage collection and are only available on the Business plan. Named snapshots differ from automatic snapshots: - They are **not garbage-collected** by snapshot retention - They persist even if the source database they are associated with is deleted - They can be referenced directly by name when restoring or cloning a database - Snapshot names must be unique per user - They can only be deleted by removing the name, after which they are picked up by garbage collection Named snapshots are intended for **long-lived backups** and are the recommended mechanism for durable recovery points. Named snapshots can be used with the [`ALTER DATABASE SET SNAPSHOT`](/sql-reference/motherduck-sql-reference/alter-database-snapshot) command, as well as the [`CREATE DATABASE FROM`](/sql-reference/motherduck-sql-reference/create-database) command to specify the snapshot you want to use. ## Restoring a database You can [restore a database](/docs/sql-reference/motherduck-sql-reference/create-database/#source-database-options) from a snapshot by specifying the snapshot name, `snapshot_id` or a timestamp. When using a timestamp the latest snapshot at or before that time will be selected. ```sql CREATE DATABASE example_db_from_snap FROM example_db (SNAPSHOT_NAME 'snap'); CREATE DATABASE example_db_from_id FROM example_db (SNAPSHOT_ID '4bfbd992-e586-48ab-9176-8dfb2d2c30b4'); CREATE DATABASE example_db_from_ts FROM example_db (SNAPSHOT_TIME '2026-01-01 00:00:01.234567'); ``` ## Snapshot features per plan ### Native storage databases | Plan | Automatic 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 | ### DuckLake databases DuckLake databases manage snapshots through [auto maintenance](/concepts/ducklake#auto-maintenance) rather than the native storage snapshot system. | Database type | Auto maintenance default | Snapshot retention default | Configurable retention | |------|----------------------------|-------------------------------|-----------------| | **Fully managed** | Enabled | Infinite (`NULL`) | Yes, with `SNAPSHOT_RETENTION_DAYS` | | **BYOB** | Disabled | Infinite (`NULL`) | Yes, after enabling `AUTO_MAINTENANCE` | ## Snapshot retention The `snapshot_retention_days` database setting controls how long historical snapshots are retained for [data recovery](/concepts/data-recovery). This setting determines how much data is stored as [`historical_bytes`](/concepts/storage-lifecycle) in your storage footprint. - **`0` days:** No historical snapshots are accessible; automatic snapshots are immediately eligible for garbage collection - **`1+` days:** Automatic snapshots created within the retention window can be accessed and restored Users can modify snapshot retention at any time using [`ALTER DATABASE`](/sql-reference/motherduck-sql-reference/alter-database): ```sql ALTER DATABASE my_database SET SNAPSHOT_RETENTION_DAYS = 4; ``` To see your database's current snapshot retention, use [`md_information_schema.databases`](/sql-reference/motherduck-sql-reference/md_information_schema/databases) and look for the `historical_snapshot_retention` field. ::::note Snapshot retention days are inherited when cloning a database. :::: ::::important Increasing `snapshot_retention_days` does not restore previously deleted snapshots. Once the garbage collection process removes a snapshot, it cannot be recovered through this setting. :::: ## Working with named snapshots Named snapshots are subject to naming rules. - Snapshot names must be 1–255 characters long - Names are unique per user across all databases - If a name includes special characters (such as `.` or `/`), wrap it in double quotes - If you create two named snapshots in a row without any new writes, the second can fail because the latest snapshot already has a name ### Renaming a named snapshot Users can change the name of an existing named snapshot using the [`ALTER SNAPSHOT`](/sql-reference/motherduck-sql-reference/alter-snapshot) command: ```sql ALTER SNAPSHOT SET snapshot_name = ''; ``` ### Deleting (un-naming) a named snapshot To remove a name from a snapshot, run the following command: ```sql ALTER SNAPSHOT SET snapshot_name = ''; ``` Once unnamed, the snapshot will become subject to the database's `snapshot_retention_days` policy and will be deleted automatically when it falls outside the retention window. ## Historical snapshots and failsafe bytes It's important to understand the distinction between historical snapshots and failsafe data: - **Historical snapshots** are point-in-time copies of your database that you can restore yourself using SQL commands. They are stored as `historical_bytes` and controlled by your `snapshot_retention_days` setting. - **Failsafe data** is a system-managed backup that MotherDuck retains for disaster recovery. It is stored as `failsafe_bytes` and can only be restored by contacting MotherDuck support. | | Historical Snapshots: `historical_bytes` | Failsafe Data: `failsafe_bytes` | |---|---|---| | **Purpose** | User-initiated data recovery and point-in-time restore | System-level disaster recovery backup | | **Controlled by** | `snapshot_retention_days` setting | MotherDuck system (7 days for standard databases, 1 day for transient) | | **Recovery method** | Self-service through [`ALTER DATABASE SET SNAPSHOT`](/sql-reference/motherduck-sql-reference/alter-database-snapshot) or [`CREATE DATABASE FROM`](/sql-reference/motherduck-sql-reference/create-database) | Requires contacting [MotherDuck support](https://motherduck.com/contact-us/support/) | | **Visibility** | Queryable through [`md_information_schema.database_snapshots`](/sql-reference/motherduck-sql-reference/md_information_schema/database_snapshots) | Not directly visible to users | | **Storage billing** | Billed as `historical_bytes` | Billed as `failsafe_bytes` | For more details on storage lifecycle stages, see [Storage Lifecycle and Management](/concepts/storage-lifecycle). ## Best practices - Use **named snapshots** for long-lived backups you may need to restore far into the future - If you frequently overwrite your data, use a short snapshot retention window (1-7 days) to avoid storing multiple copies of the same data - Failsafe restores should be thought of as a precautionary, last-minute measure in exception scenarios only; we recommend using historical snapshots for routine recovery needs - Do **not** use [transient databases](/concepts/storage-lifecycle#transient-databases) for critical or hard-to-reconstruct data ## Related content - [Data Recovery](/concepts/data-recovery) — Step-by-step guide to restoring databases from snapshots - [Storage Lifecycle and Management](/concepts/storage-lifecycle) — Understanding storage stages and billing - [`CREATE SNAPSHOT`](/sql-reference/motherduck-sql-reference/create-snapshot) — SQL reference for creating snapshots - [`DATABASE_SNAPSHOTS` view](/sql-reference/motherduck-sql-reference/md_information_schema/database_snapshots) — Query snapshot history and metadata - [`CREATE DATABASE` from a snapshot](/docs/sql-reference/motherduck-sql-reference/create-database/#source-database-options) --- ## Docs feedback MotherDuck accepts optional user-submitted feedback about this page at `POST https://motherduck.com/docs/api/feedback/agent`. For agents and automated tools, feedback submission should be user-confirmed before sending. Payload: ```json { "page_path": "/concepts/database-snapshots/", "page_title": "Database Snapshots", "text": "", "source": "" } ``` `page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": ""}`, `400` for malformed payloads, and `429` when rate-limited.