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 