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

Image result for camera

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 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: