What is Source_Database_ID in Sys.Databases?- Interview Question of the Week #232

Question: Why is source_database_id in sys.databases usually NULL? It identifies the source of a database snapshot. NULL means the database is not a database snapshot.

A workshop attendee kept seeing NULL and asked a good question: if she had just created a database, where was its source? The column’s name sounds more general than its purpose. It does not record the template, backup or database from which an ordinary database was copied.

SELECT database_id, name, source_database_id,
       DB_NAME(source_database_id) AS SourceDatabase
FROM sys.databases
ORDER BY database_id;

In the original example, I created SQLAuthority, then a snapshot named SQLAuthority_Snapshot. The second inventory makes the relationship visible:

If you reproduce the snapshot

-- Inspect logical DATA file names before planning a snapshot.
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID(N'SQLAuthority') AND type = 0;

-- Historical single-data-file example. Change names/path for your lab.
-- CREATE DATABASE SQLAuthority_Snapshot ON
-- (NAME = N'SQLAuthority', FILENAME = N'D:\Data\SQLAuthority_Snapshot.ss')
-- AS SNAPSHOT OF SQLAuthority;

The commented example assumes one data file with the logical name SQLAuthority. Inspect the actual file names and provide a snapshot file for every source data file. The server’s folder must already exist and be writable by the SQL Server service. A filename on your SSMS computer is not a server path.

A database snapshot is a read-only view at a point in time on the same SQL Server instance. It depends on its source and uses copy-on-write sparse files as the source changes. It is not an independent backup. Creating a normal database or restoring a backup does not make source_database_id non-NULL.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.