Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136

    Unanswered: How to check if a database is a snapshot

    Hi everyone,
    I am looking for a way to check to see if a database is a snapshot or not. You can for example check different properties of a database by running the following:

    Code:
    SELECT DATABASEPROPERTYEX('databasename','Recovery') 
    SELECT DATABASEPROPERTYEX('databasename','IsInStandBy')
    SELECT DATABASEPROPERTYEX('databasename','Status')
    So I am trying to see if there is a way to check for a snapshot

    Thanks,
    Reghardt

  2. #2
    Join Date
    May 2007
    Posts
    49
    I think you can use sytem view - sys.databases

    if exists(Select * from sys.databases where name = '<your_db>' and source_database_id is not null)
    Begin
    -- Snapshot database
    End

    not sure whether this is a correct way or not but the thing I noticed is that source_database_id column always contains value if it is a snapshot db.
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  3. #3
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136

    Talking Thanks!

    It works great! Thanks a lot

    Code:
    if exists(Select * from master.sys.databases where source_database_id is not null)
     select [name] + ' is a snapshot' from master.sys.databases where source_database_id is not null
    else
     print 'no snapshot found'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •