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 MSDB
GO

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
--WHERE RSH.destination_database_name='Your Database Name'
ORDER BY 2 ASC, RSH.restore_date DESC

Output/Result