SQL Server: Finding the Source database name/id against which Database Snapshot was created

Hi Friends,

Interesting question – suppose a DBA has created a snapshot on a user database. You do not have access to the source/user database. You only have access to the Snapshot database. Is there a way you can find out against which database was the snapshot DB created?

Here is the answer:

--by Amit
-- create a sample database
create database SDTest
Go
 
-- create a snapshot
CREATE DATABASE SDTest_dbss1800 ON
( NAME = SDTest, FILENAME = 
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2k8R2\MSSQL\Data\SDTest_dbss1800_data_1800.ss' )
AS SNAPSHOT OF SDTest;
GO
 
--find the the source DB information from sys.databases 
select source_database_id from sys.databases where name = 'SDTest_dbss1800'

You will observer the output of the last statement. sys.databases catalog view contains a column called source_database_id, wihihc contains the DB ID of the source database, and through this you can extract all the information you need.

One of our SQL MVPs, Denis, also pointed out that sp_helpdb can also be used here to track the datafiles of the source DB.

 

 

Amit Bansal

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

Leave a Reply

Your email address will not be published. Required fields are marked *