Thursday, September 22, 2011

How to find Database Snapshots associated with a Database


Often times, you might want to find out a list of all the existing database snapshots. Here is a simple script that will return each database and the associated snapshots.
USE MASTER
          SELECT  SourceDB.[name] AS SourceDatabase,
                  SnapshotDB.[name] AS SnapshotDatabase,
                  SnapshotDB.Create_Date AS DateOfCreation
          FROM sys.databases SourceDB
          INNER JOIN sys.databases SnapshotDB
          ON SourceDB.database_id = SnapshotDB.source_database_id
I have two database snapshots on my SQL Server, so the above script will return two rows as shown in the figure below.

No comments:

Post a Comment