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 Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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'

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.

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'

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






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems