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.
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.
