Restore history

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 = 'daniel@glutenfree.com'
,@subject = 'Restore completed'
,@body = @text

END
Advertisements
This entry was posted in Database Administration and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s