Take the 2-minute tour ×
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. It's 100% free, no registration required.

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>') 
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.

share|improve this question
3  
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

1 Answer 1

up vote 7 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.

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   
share|improve this answer

Your Answer

 
discard

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.