Introduction

This script will help you to get the restore history for all the databases. If you need to know the restore history only for a single database then please use the WHERE CLAUSE.

Script

-- ============================
-- Owner: SOYELUDDIN BISWAS
-- Created Date: 27/09/2019
-- Email: st.biswas99@gmail.com
-- ============================

-- Use the MSDB database
USE MSDB
GO

-- Retrieve the server name
SELECT @@SERVERNAME AS SERVER
	,RSH.destination_database_name AS [Database]
	,BKS.server_name AS [Source Server]
	,CASE 
		WHEN RSH.restore_type = 'D' THEN 'Full Backup'
		WHEN RSH.restore_type = 'L' THEN 'Log Backup'
		WHEN RSH.restore_type = 'I' THEN 'Differential Backup'
	END AS [Restore type]
	,RSH.user_name AS [Restored By]
	,RSH.restore_date [Restore Date]
	,BKS.backup_start_date AS [Backup Start time]
	,BKS.backup_finish_date AS [Backup Finish time]
	,BKS.user_name AS [Backup Taken By]
FROM dbo.restorehistory RSH
INNER JOIN dbo.backupset BKS ON RSH.backup_set_id = BKS.backup_set_id

-- Uncomment and modify the following line to filter by a specific database name
-- WHERE RSH.destination_database_name = 'Your Database Name'

-- Order the results by database name and restore date
ORDER BY 2 ASC, RSH.restore_date DESC

Output/Result