4

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?

3 Answers 3

4

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.
1

SQL Server does not store the start and end times of a restore like it does for a backup (in the backupset table). The best you can do is to either A) create a log table that you insert into when you start a restore and then update when done or B) you could use the default trace in SQL Server. If you have not changed any of the settings and it's still running it will capture restores. However, its not very easy to use as seen in the link above.

1

Simple way to query the end time:

use MSDB
go
select * from restorehistory;

Or

use MSDB   
go

SELECT MAX(restore_date) as LAST_RESTORE_DT 
FROM restorehistory
WHERE destination_database_name = 'DB_NAME'

The restorehistory table doesn't have the start time of the refresh.

1
  • This appears to give the time the restore started not the time it finished Jul 23, 2019 at 2:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.