SQL Server Restore – How to Get SQL Server Restore Start and End Time Information?

backuprestoresql server

SQL Server error log reports only the end of the restore (ah doc restore)
How can I get the start time? Is it stored somewhere?

Best Answer

1.The restore history information is readily available inside the msdb i.e. msdb.dbo.restorehistory

You can use below T-SQL code to find the start time and restore information of a database over a required period.

DECLARE @dbname sysname, @days int
SET @dbname = NULL --substitute for whatever database name you want
SET @days = -1 --previous number of days, script will default to 30
SELECT
 rsh.destination_database_name AS [Database],
 rsh.user_name AS [Restored By],
 CASE WHEN rsh.restore_type = 'D' THEN 'Database'
 WHEN rsh.restore_type = 'F' THEN 'File'
 WHEN rsh.restore_type = 'G' THEN 'Filegroup'
 WHEN rsh.restore_type = 'I' THEN 'Differential'
 WHEN rsh.restore_type = 'L' THEN 'Log'
 WHEN rsh.restore_type = 'V' THEN 'Verifyonly'
 WHEN rsh.restore_type = 'R' THEN 'Revert'
 ELSE rsh.restore_type 
 END AS [Restore Type],
 rsh.restore_date AS [Restore Started],
 bmf.physical_device_name AS [Restored From], 
 rf.destination_phys_name AS [Restored To]
 FROM msdb.dbo.restorehistory rsh
 INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
 INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
 INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
 WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) --want to search for previous days
 AND destination_database_name = ISNULL(@dbname, destination_database_name) --if no dbname, then return all
 ORDER BY rsh.restore_history_id DESC
  1. Alternatively you can use the approach of default trace as mentioned by Chris as well.
Related Question