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.