For MSDB Restorehistory column restore_date, the SQL documentation claims that this means “Date and time of the completion of the restore operation.” However, this doesn’t appear to be the case. It shows the start time.
This is bad, because I’m trying to kick off an email based on the restore completion time. It is looking for restores completing in the past 15 minutes. It wasn’t finding any, because this restore_date is over an hour ago, even though the restore just completed.
Another indication that a restore took place, is that my job took more than a minute. And my email gets kicked off in the second step. So it will now check to see if the job has been running more than an hour, and if so, it will send the email. Here is the new code.
DECLARE @RunTime int SELECT @RunTime = DATEDIFF(N, start_execution_date, GETDATE()) FROM msdb.dbo.sysjobactivity AS sja INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id WHERE sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL AND sj.name = 'Restore Databases' IF @RunTime > 60 BEGIN DECLARE @text NVARCHAR(2000) SET @text = 'The restore completed on ' + @@SERVERNAME EXECUTE msdb.dbo.sp_send_dbmail @profile_name = 'DBMailProfile' , @recipients = 'firstname.lastname@example.org' ,@subject = 'Restore completed' ,@body = @text END