TSQL SCRIPT

• eobiki10

  SSCrazy

  Points: 2390

  More actions

  September 18, 2014 at 10:08 am

  #311069

  Hi All,

  I have this script which helps me to know my last restore date on my database. My database restore normally occurs at 20:30.

  SELECT TOP 1 *

  FROM RESTOREHISTORY

  WHERE DESTINATION_DATABASE_NAME = 'intelligence'

  ORDER BY RESTORE_DATE DESC

  I want to be able to modify the script in such a way that if my next restore is lagging for more than 24 hours then send an email.

  Thanks,

  Emma

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  September 18, 2014 at 11:20 am

  #1746497

  Maybe something like this (you should read about sp_send_dbmail):

  IF NOT EXISTS(

  SELECT *

  FROM RESTOREHISTORY

  WHERE DESTINATION_DATABASE_NAME = 'intelligence'

  AND RESTORE_DATE > DATEADD(DD, -1, GETDATE())

  )

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name = 'Adventure Works Administrator',

  @recipients = 'danw@Adventure-Works.com',

  @body = 'The stored procedure finished successfully.',

  @subject = 'Automated Success Message' ;

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• eobiki10

  SSCrazy

  Points: 2390

  More actions

  September 18, 2014 at 2:10 pm

  #1746536

  Thanks. Not yet tested but that looks like something that I need.

  Emma