Day: February 11, 2021

This script will create a SQL view to select the last restore date for every database on a Microsoft SQL Server along with the user who performed the restore.

It has included a couple of other use columns in the returned data such as the collation_name and compatibility_level:

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_GetLastDatabaseRestoreDate', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_GetLastDatabaseRestoreDate
GO
-- create view
CREATE VIEW uv_AZRCRV_GetLastDatabaseRestoreDate AS
WITH LastRestores AS
	(
		SELECT
			d.name AS DatabaseName
			,d.create_date
			,d.compatibility_level
			,d.collation_name
			,r.restore_date
			,r.user_name
			,ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC) AS RowNumber
		FROM
			master.sys.databases AS d
		LEFT JOIN
			msdb.dbo.[restorehistory] AS r
				ON
					r.[destination_database_name] = d.Name
	)

SELECT
	DatabaseName
	,create_date
	,compatibility_level
	,collation_name
	,restore_date
	,user_name
FROM
	LastRestores
WHERE
	RowNumber = 1
GO

GRANT SELECT ON uv_AZRCRV_GetLastDatabaseRestoreDate TO DYNGRP
GO

I created this as a view so that it could easily be included in a SmartList using either SmartList Designer or SmartList Builder to allow finance users to see how up-to-date their test system is.