snippetsqlMajor

How can I query for existing database snapshots in SQL server?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

canserversqlquerysnapshotsdatabaseforhowexisting

Problem

I'm trying to create a t-sql query that can determine whether or not a given database has any database snapshots that were created from it.

For example, if I were to create a snapshot like this:

CREATE DATABASE [DatabaseA_Snapshot] ON 
(NAME=DatabaseA, FileName='') 
AS SNAPSHOT OF [DatabaseA]

Is there a way I can query for the existence of that snapshot again later? I can see that it shows up in sys.databases, but I couldn't find any information that would help me determine that it's a database snapshot that was created from DatabaseA.

SQL Server Management Studio's Object Explorer places it under the 'Database Snapshots' folder so there's obviously some way to differentiate these from regular databases.

Solution

A snapshot database will have an entry in sys.databases.source_database_id, so you can use something like the following to check for a current snapshot. Similarly, you could use the same check to DROP an existing snapshot before creating new.

IF NOT EXISTS(
    SELECT 
        * 
    FROM 
        sys.databases 
    WHERE 
        name = 'DatabaseA_Snapshot' 
    AND source_database_id IS NOT NULL
    )
BEGIN
    CREATE DATABASE [DatabaseA_Snapshot] 
    ON (NAME=DatabaseA, FileName='') 
    AS SNAPSHOT OF [DatabaseA]
END

Code Snippets

IF NOT EXISTS(
    SELECT 
        * 
    FROM 
        sys.databases 
    WHERE 
        name = 'DatabaseA_Snapshot' 
    AND source_database_id IS NOT NULL
    )
BEGIN
    CREATE DATABASE [DatabaseA_Snapshot] 
    ON (NAME=DatabaseA, FileName='<whatever>') 
    AS SNAPSHOT OF [DatabaseA]
END

Context

StackExchange Database Administrators Q#35105, answer score: 20

Revisions (0)

No revisions yet.