Get All Database Snapshots and Their Parent Databases On A SQL Server

This is going to be a quick post but this morning I was asked the question, ‘how can I get the name of all the database snapshots on a SQL Server?’. It was an interesting question so I thought I’d share a quick script to do just that.

We’re going to need to tap into sys.databases to grab this information, sys.databases holds a row for not only every database on our server but also all the snapshots.

The key to this is the source_database_id column, this contains the database ID of a snapshot’s parent (or source) database. The majority of the time, this column will be NULL, indicating that this is actually a database but any time we see a value in the column, we know it’s going to be a snapshot.

Knowing that, knocking together a script to return all database snapshots and their parent databases is pretty simple.

SELECT snapshots.name AS SnapshotName, databases.name AS ParentDatabase
FROM sys.databases snapshots
JOIN sys.databases databases ON snapshots.source_database_id = databases.database_id
WHERE snapshots.source_database_id IS NOT NULL

Thanks for reading 🙂