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. The most recent snapshot for each database is considered 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 Name | Data Type | Value |
|---|---|---|
| DATABASE_NAME | VARCHAR | The name of the database |
| DATABASE_ID | UUID | Unique ID for the database |
| SNAPSHOT_ID | UUID | Unique ID for the snapshot |
| SNAPSHOT_NAME | VARCHAR | Optional name for the snapshot (NULL for automatic snapshots) |
| CREATED_TS | TIMESTAMP | Time when the snapshot was created |
| ACTIVE_BYTES | UBIGINT | The logical size of the database at this snapshot |
| BYTES_WRITTEN | UBIGINT | The number of bytes written since the previous snapshot |
| BYTES_DELETED | UBIGINT | The number of bytes deleted since the previous snapshot |
| OWNER_NAME | VARCHAR | The database owner (user or service account) |
| OWNER_ID | UUID | Unique 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 = 'my_db'
AND snapshot_name IS NOT NULL
ORDER BY created_ts DESC;
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.