Finding a History of Database Snapshots, Taken and Restored

All code in this post can be found in our GitHub repo https://github.com/SQLUndercover/UndercoverToolbox

This is a question that’s come up twice this morning, firstly where can we find a history of database snapshots and secondly where can we find a history of restores from snapshot?

Frustratingly, SQL doesn’t make this at all easy for us and if this is something that you want to record, you’re going to have a do a little extra work.

Let’s take a look at each part in turn.

Getting a History of Database Snapshots Taken

In my head, a database snapshot is a form of backup. At least that’s how I tend to use them, as a quick and easy rollback method when there’s some database change occurring. So logically, the first place that I’m going to go to when looking for a history of backups is going to be msdb.dbo.backupset.

But if you have a poke around in there then you’ll find no sign of your snapshot getting created. Ok, let’s be fair to SQL here, a snapshot isn’t technically a backup so we can let it off that one. Perhaps there’s a specific DMV for snapshot information? Nope!

So what can we do? Well there are a few places that we can get some information. Firstly sys.databases, not only stores details of databases on our server but also snapshots.

The following query will give you some details on all the snapshots that are currently on your server, including the creation date and the id of the source database.

SELECT name, create_date, database_id, source_database_id 
FROM sys.databases
WHERE source_database_id IS NOT NUL

That’s a start, but it’s only giving us info on existing snapshots. If you want to build up a history, including snapshots that may not exist anymore then you’ll need to find a way to monitor and possibly audit sys.databases. (HINT: keep an eye out for an upcoming upgrade to our own SQL Undercover Catalogue, which will include a new snapshots module to hopefully try to address this issue).

Getting a History of Restores from Snapshot

For this, I was pretty confident that good old trusty, msdb.dbo.restorehistory would be the place to go. But looking in there, I could see no sign of any restores from snapshot. I couldn’t believe that was true, so I checked the documentation and sure enough, there’s no snapshot code letter for restore_type.

The only place that you’re going to find any record of a restore from snapshot is in SQL’s logs where I found.

Reverting database ‘SQLUndercover’ to the point in time of database snapshot ‘SQLUndercover_snapshot’ with split point LSN 84000000117300001 (0x00000054:00000495:0001). This is an informational message only. No user action is required.

So, like the first question this one looks like another case where you’re going to have to do a little extra work to catch those messages and store them somewhere.

Something like this will do the trick,

Create a table to store everything in

CREATE TABLE SnapshotRestoreHistory
(LogDate DATETIME,
ProcessInfo VARCHAR(10),
[Text] VARCHAR(500) NOT NULL)

And then running the following proc will grab any ‘Reverting database’ messages in the log and pop them into that table. It’ll grab all unrecorded messages from the current log as well as all previous ones.

CREATE PROC PopulateSnapshotRestoreHistory

AS

BEGIN

	--create temp holding table for log entries
	IF OBJECT_ID('tempdb.dbo.#SnapshotLogs') IS NOT NULL
	DROP TABLE #SnapshotLogs

	CREATE TABLE #SnapshotLogs
	(LogDate DATETIME,
	ProcessInfo VARCHAR(10),
	[Text] VARCHAR(500) NOT NULL)


	--temp table to be userd by Log sursor
	IF OBJECT_ID('tempdb.dbo.#LogFiles') IS NOT NULL
	DROP TABLE #LogFiles

	CREATE TABLE #LogFiles (LogNumber INT, StartDate DATETIME, SizeInBytes INT)

	INSERT INTO #LogFiles
	EXEC xp_enumerrorlogs 

	DECLARE @LogNumber INT
	DECLARE LogCur CURSOR LOCAL FAST_FORWARD FOR
	SELECT LogNumber
	FROM #LogFiles

	OPEN LogCur

	FETCH NEXT FROM LogCur INTO @LogNumber

	WHILE @@FETCH_STATUS = 0

	BEGIN
		--get entries from log file
		INSERT INTO #SnapshotLogs
		EXEC [sys].[sp_readerrorlog] @LogNumber,1,'Reverting database'

		--merge log entries in to history table
		MERGE SnapshotRestoreHistory AS Target
		USING #SnapshotLogs
		ON Target.LogDate = #SnapshotLogs.LogDate
			AND Target.[Text] = #SnapshotLogs.[Text]
		WHEN NOT MATCHED BY Target THEN
		INSERT (LogDate,ProcessInfo,[Text])
		VALUES (#SnapshotLogs.LogDate,#SnapshotLogs.ProcessInfo,#SnapshotLogs.[Text]);

		FETCH NEXT FROM LogCur INTO @LogNumber
	END 

	CLOSE LogCur
	DEALLOCATE LogCur
END

If you’re looking for a way to build a history of database snapshots, SQL doesn’t give us much so I hope that this has helped somewhat.