SQL Server Gathering Restore Times and Durations

Finding the actual time a database is restored is not as simple as querying a system table. The closest thing to gathering this information resides in the msdb table restorehistory. Unfortunately the restore_date field here is actually the time the restore starts. To get when the restore completed you need to go to the sql server error log. To automatically get at this information I’ve written a quick query that utilizes the very well know undocumented system xp xp_readerrorlog. There are two scripts below the first just gets you the actual data out of the log, nothing real fancy. The second finds out how long the backup actually took by joinning msdb..restoryhistoy

Script 1 Just get the Data
Create table #RestoreHistory
(LogDate datetime,
ProcessInfo varchar(10),
TextOut varchar(100))


Declare @DBName Varchar(100)
Set @DBName = 'DataBaseName'

insert into #RestoreHistory
EXEC master.dbo.xp_readerrorlog 0, 1, 'Starting up database' , @DBName, NULL, NULL, N'desc'
Select * from #RestoreHistory order by LogDate desc



Script 2 Find out how long the Restore took
This just extends the first query a bit

Create table #RestoreHistory
(LogDate datetime,
ProcessInfo varchar(10),
TextOut varchar(100))


Declare @DBName Varchar(100)
Set @DBName = 'DatabaseName'

insert into #RestoreHistory
EXEC master.dbo.xp_readerrorlog 0, 1, 'Starting up database' , @DBName, NULL, NULL, N'desc'




Declare @Starttime datetime
select top 1 @Starttime = restore_date from msdb..restorehistory
where destination_database_name = @DBName
order by restore_date desc




select top 1 @DBName as DBName, @StartTime, LogDate as FinishTime,
Datediff( mi, @StartTime, LogDate) as RestoreTimeInMin
from #RestoreHistory --order by LogDate Desc