Find the Last Time Databases or Logs were Restored

I have a client that uses a lot of disconnected log shipping on a few servers.  They do this because they want to have a copy of their database that is actually hosted by software vendors for reporting purposes.  So, disconnected log shipping it is!  We pull down files that have been FTP’d to the corporate FTP site and apply those logs locally and leave the databases in standby so that they can be queried.

(If you want to review how to set up disconnected log shipping complete with FTP download scripts, all SQL jobs and log shipping monitoring, then hop on over to https://www.sqlrx.com/setting-up-disconnected-log-shipping/ to check it out!)

Of course every so often there is a glitch and one or all databases can get out of synch pretty easily.  When we receive a notification that tlogs have not been restored in a while, the hunt is on to see what happened and get it corrected.  Since I have disconnected log shipping set up, I can’t get a log shipping report from SSMS that will tell me errors and what was the last log applied.  So, I made a query that will give me the most recent file that was restored along with the file name, date, database, etc.  I can pull back all databases or by uncommenting a line can filter by a single or multiple databases.

WITH LastRestore AS
(SELECT ROW_NUMBER() OVER (PARTITION BY h.destination_database_name ORDER BY h.restore_date DESC) AS RowNum,
	h.restore_date AS RestoreDate, 
	bs.database_name AS DBName, 
	CASE h.restore_type WHEN 'D' THEN 'Database'
	  WHEN 'F' THEN 'File'
	  WHEN 'G' THEN 'Filegroup'
	  WHEN 'I' THEN 'Differential'
	  WHEN 'L' THEN 'Log'
	  WHEN 'V' THEN 'Verifyonly'
	  WHEN 'R' THEN 'Revert'
	  ELSE h.restore_type 
	END AS RestoreType,
	CASE h.recovery WHEN 1 THEN CAST('WITH RECOVERY' AS VARCHAR(20))
		WHEN 0 THEN CAST('NORECOVERY' AS VARCHAR(20))
		ELSE CAST(h.recovery AS VARCHAR(20))
	END AS [Recovery],
	bm.physical_device_name AS RestoredFromFile,
	rf.destination_phys_name AS PhysicalDBFileLocation,
	h.user_name AS RestoredByA
	--,bs.first_lsn AS FirstLSN
	--,bs.last_lsn AS LastLSN
FROM msdb.dbo.restorehistory h 
JOIN msdb.dbo.restorefile rf ON h.restore_history_id = rf.restore_history_id
JOIN msdb.dbo.backupset bs ON h.backup_set_id = bs.backup_set_id
JOIN msdb.dbo.backupmediafamily bm ON bs.media_set_id = bm.media_set_id
)
SELECT *
FROM LastRestore
WHERE RowNum = 1
--AND DBName IN ('Database1','Database2','Database3')
ORDER BY DBName ASC

The nice thing about this query is that it can be used for anything (not just log shipped databases) as I show in my results above.  So, if you have a dev or staging system and were wondering when a database was last refreshed, you will find it with this query.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!