Dives: Composable visualizations built by your favorite agentsJoin the livestream

Skip to main content

DATABASE_SNAPSHOTS view

The MD_INFORMATION_SCHEMA.DATABASE_SNAPSHOTS view provides information about database snapshots for databases visible to the current user. This can be used for data recovery or to inspect snapshot history.

Schema​

When you query the MD_INFORMATION_SCHEMA.DATABASE_SNAPSHOTS view, the results contain at least one row per database visible to the user. MotherDuck treats the most recent snapshot for each database as the "active" snapshot.

In addition, if you have historical snapshots or named snapshots associated with any of your databases, they will show up here too.

The MD_INFORMATION_SCHEMA.DATABASE_SNAPSHOTS view has the following schema:

Column NameData TypeValue
DATABASE_NAMEVARCHARThe name of the database
DATABASE_IDUUIDUnique ID for the database
SNAPSHOT_IDUUIDUnique ID for the snapshot
SNAPSHOT_NAMEVARCHAROptional name for the snapshot (NULL for automatic snapshots)
CREATED_TSTIMESTAMPTime when the snapshot was created
ACTIVE_BYTESUBIGINTThe logical size of the database at this snapshot
BYTES_WRITTENUBIGINTThe number of bytes written since the previous snapshot
BYTES_DELETEDUBIGINTThe number of bytes deleted since the previous snapshot
USER_NAMEVARCHARThe user who owns the database corresponding to the snapshot (user or service account)
USER_IDUUIDUnique ID of the database owner

Example usage​

FROM MD_INFORMATION_SCHEMA.DATABASE_SNAPSHOTS
LIMIT 10;

Get the active (most recent) snapshot for each database:

SELECT *
FROM MD_INFORMATION_SCHEMA.DATABASE_SNAPSHOTS
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY database_id
  ORDER BY created_ts DESC
) = 1
ORDER BY database_name;

List named snapshots for a specific database:

SELECT *
FROM MD_INFORMATION_SCHEMA.DATABASE_SNAPSHOTS
WHERE database_name = 'example_db'
  AND snapshot_name IS NOT NULL
ORDER BY created_ts DESC;

View snapshots with retention settings​

Combine DATABASE_SNAPSHOTS with DATABASES to see all snapshots for a database along with its retention period:

SELECT
    s.database_name,
    d.historical_snapshot_retention,
    s.snapshot_id,
    s.snapshot_name,
    s.created_ts,
    s.active_bytes
FROM MD_INFORMATION_SCHEMA.DATABASE_SNAPSHOTS s
JOIN MD_INFORMATION_SCHEMA.DATABASES d
    ON s.database_id = d.uuid
WHERE s.database_name = 'example_db'
ORDER BY s.created_ts DESC;

This query helps you understand which snapshots are available for point-in-time restore and how long automatic snapshots will be retained before garbage collection.

Admin: View all named snapshots in your organization​

Admins can use STORAGE_INFO combined with DATABASE_SNAPSHOTS to see all named snapshots across the organization:

SELECT
    s.user_name,
    s.database_name,
    s.snapshot_name,
    s.snapshot_id,
    s.created_ts,
    s.active_bytes
FROM MD_INFORMATION_SCHEMA.DATABASE_SNAPSHOTS s
WHERE s.snapshot_name IS NOT NULL
ORDER BY s.user_name, s.database_name, s.created_ts DESC;
Snapshot naming conflicts

Snapshot names must be unique per user, but different users can have snapshots with the same name. When restoring or referencing snapshots programmatically, use user_id or user_name along with snapshot_name to avoid ambiguity.

Understanding timestamps​

Use DATABASE_SNAPSHOTS.created_ts for snapshot creation times — this is what you need for point-in-time restore operations with ALTER DATABASE SET SNAPSHOT.

Use STORAGE_INFO.created_ts for database creation times — this is useful for billing and lifecycle management.

Viewcreated_ts meaningUse case
DATABASE_SNAPSHOTSWhen the snapshot was createdPoint-in-time restore, finding snapshots to recover
STORAGE_INFOWhen the database was createdStorage billing, database lifecycle management

Troubleshooting​

Why do I only see one snapshot for my database?​

If your database's retention is set to zero days, you will only be able to see the "active" snapshot associated with your database. To modify your database's retention see ALTER DATABASE.

Why can't I find a snapshot from a specific time?​

Automatic snapshots are only retained for the duration specified by snapshot_retention_days and when data is actively modified. Once a snapshot falls outside this window, it is garbage-collected and cannot be recovered. To check your database's retention setting:

SELECT name, historical_snapshot_retention
FROM MD_INFORMATION_SCHEMA.DATABASES
WHERE name = 'example_db';

If you want to keep snapshots when only querying a database, you can either use a named snapshot or actively create a snapshot with:

CREATE SNAPSHOT OF example_db;

How can I see the current retention period for my database​

You can find the historical retention period in the information schema:

select name, historical_snapshot_retention from md_information_schema.databases

See also​