Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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='<whatever>') 

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.

share|improve this question
Please start using sys.databases instead of sys.sysdatabases as the latter is deprecated. – Jon Seigel Feb 20 '13 at 18:01
@JonSeigel good to know. Fixed. – Jesse Taber Feb 20 '13 at 18:38
up vote 9 down vote accepted

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.

        name = 'DatabaseA_Snapshot' 
    AND source_database_id IS NOT NULL
    CREATE DATABASE [DatabaseA_Snapshot] 
    ON (NAME=DatabaseA, FileName='<whatever>') 
    AS SNAPSHOT OF [DatabaseA]
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.