SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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.

Database snapshot listed in SQL Server Management Studio

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'

 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.

check if database snapshot is created on SQL Server using SQL script
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.

query original database from snapshot on SQL Server

declare @database_name sysname = 'AdventureWorks2014_SS'

 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

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.