Check Existence of Snapshot for a Database on SQL Server
SQL tutorial shows how to find if a database snapshot is already created for a specific SQL Server database. If you have high number of databases and your Database Snaphots node on SQL Server Management Studio is filled with numerous database snapshots, it is best to check if a snapshot of the database is created using SQL script.
As seen in below screenshot of SQL Server Management Studio (SSMS) Object Explorer window, under Database Snapshots node of target SQL Server instance there may be many databases making it difficult to search for a specific database snapshot.
Here is an example SQL script code where I query sys.databases system view for database properties for a sample database.
Assume that SQL programmer is trying to find out if a database snapshot is created for database named Kodyaz.
declare @database_name sysname = 'Kodyaz'
Select
ss.database_id as Snapshot_Database_Id,
ss.name as Snapshot_Database,
ss.source_database_id as Source_Database_Id,
s.name Source_Database
From sys.databases ss -- snapshot database
Inner Join sys.databases s -- source database
On ss.source_database_id = s.database_id
Where s.name = @database_name
As seen above I join sys.databases to itself by using source_database_id and database_id column values.
The snapshot database when it is created refers to its original source database using the source_database_id field on sys.databases tables.
Here the snapshot database name and its source database is listed as a result of above SQL Select statement on sys.databases SQL Server system view
If you don't have a good naming convention, it might be difficult to find the source database for a snapshot database as well.
In such a case, you can reverse the above script and this time query for the source SQL database by using the snapshot database name as seen in below SQL code.
declare @database_name sysname = 'AdventureWorks2014_SS'
Select
ss.database_id as Snapshot_Database_Id,
ss.name as Snapshot_Database_Name,
s.database_id as Source_Database_Id,
s.name as Source_Database_Name
From sys.databases ss -- snapshot database
Inner Join sys.databases s -- source database
On ss.source_database_id = s.database_id
Where ss.name = @database_name