Getting a history of database snapshot creation

Earlier today someone asked on the #sqlhelp Twitter alias if there is a history of database snapshot creation anywhere, apart from scouring the error logs.

There isn’t, unfortunately, but you can dig around the transaction log of the master database to find some information.

When a database snapshot is created, a bunch of entries are made in the system tables in master and they are all logged, under a transaction named DBMgr::CreateSnapshotDatabase. So that’s where we can begin looking.

Here’s a simple example of a database snapshot:

USE [master];
GO

IF DATABASEPROPERTYEX (N'Company_Snapshot', N'Version') > 0
BEGIN
    DROP DATABASE [Company_Snapshot];
END
GO
IF DATABASEPROPERTYEX (N'Company', N'Version') > 0
BEGIN
    ALTER DATABASE [Company] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [Company];
END
GO

-- Create a database
CREATE DATABASE [Company];
GO

-- Create the snapshot
CREATE DATABASE [Company_Snapshot]
ON (NAME = N'Company', FILENAME = N'C:\SQLskills\CompanyData.mdfss')
AS SNAPSHOT OF [Company];
GO

And I can find the transaction using the following code, plus who did it and when:

USE [master];
GO

SELECT
    [Transaction ID],
    SUSER_SNAME ([Transaction SID]) AS [User],
    [Begin Time]
FROM fn_dblog (NULL, NULL)
WHERE [Operation] = N'LOP_BEGIN_XACT'
    AND [Transaction Name] = N'DBMgr::CreateSnapshotDatabase';
GO

Transaction ID User             Begin Time
-------------- ---------------- ------------------------
0000:00099511  APPLECROSS\Paul  2016/10/20 13:07:53:143

Now to get some useful information, I can crack open one of the system table inserts, specifically the insert into one of the nonclustered indexes of the sys.sysdbreg table:

SELECT
    [RowLog Contents 0]
FROM fn_dblog (NULL, NULL)
WHERE [Transaction ID] = N'0000:00099511'
    AND [Operation] = N'LOP_INSERT_ROWS'
    AND [AllocUnitName] = N'sys.sysdbreg.nc1';
GO

RowLog Contents 0
-------------------------------------------------------------------------------------
0x26230000000100290043006F006D00700061006E0079005F0053006E0061007000730068006F007400

Bytes 2 through 5 (considering the first byte as byte 1) are the byte-reversed database ID of the snapshot database, and bytes 10 through the end of the data are the sysname name of the database. Similarly, grabbing the insert log record for the nonclustered index of the sys.syssingleobjrefs table allows us to get the source database ID.

Here’s the finished code:

SELECT * FROM
(
SELECT
    SUSER_SNAME ([Transaction SID]) AS [User],
    [Begin Time]
FROM fn_dblog (NULL, NULL)
WHERE [Transaction ID] = N'0000:00099511'
    AND [Operation] = N'LOP_BEGIN_XACT'
) AS [who],
(
SELECT
    CONVERT (INT,
        SUBSTRING ([RowLog Contents 0], 5, 1) +
        SUBSTRING ([RowLog Contents 0], 4, 1) +
        SUBSTRING ([RowLog Contents 0], 3, 1) +
        SUBSTRING ([RowLog Contents 0], 2, 1)) AS [Snapshot DB ID],
    CONVERT (SYSNAME, SUBSTRING ([RowLog Contents 0], 10, 256)) AS [Snapshot DB Name]
FROM fn_dblog (NULL, NULL)
WHERE [Transaction ID] = N'0000:00099511'
	AND [Operation] = N'LOP_INSERT_ROWS'
	AND [AllocUnitName] = N'sys.sysdbreg.nc1'
) AS [snap],
(
SELECT
    CONVERT (INT,
        SUBSTRING ([RowLog Contents 0], 5, 1) +
        SUBSTRING ([RowLog Contents 0], 4, 1) +
        SUBSTRING ([RowLog Contents 0], 3, 1) +
        SUBSTRING ([RowLog Contents 0], 2, 1)) AS [Source DB ID]
FROM fn_dblog (NULL, NULL)
WHERE [Transaction ID] = N'0000:00099511'
	AND [Operation] = N'LOP_INSERT_ROWS'
	AND [AllocUnitName] = N'sys.syssingleobjrefs.nc1'
) AS [src];
GO

User             Begin Time               Snapshot DB ID Snapshot DB Name  Source DB ID
---------------- ------------------------ -------------- ----------------- ------------
APPLECROSS\Paul  2016/10/20 13:07:53:143  35             Company_Snapshot  22

I’ll leave it as an exercise for the reader to wrap a cursor around the code to operate on all such transactions, and you can also look in the master log backups using the fn_dump_dblog function (see here for some examples).

Enjoy!