Alert! Alert! Backup and Restore Baby!

  • Comments posted to this topic are about the content posted at

  • 15CriticalBackupRestoreAlerts.sql I'm not finding a way to access this script.

    Once these alerts are generated, how do you get notified? Do the DBAs read the error logs or get pages, etc?




  • Michelle,

    I will talk to someone at about the script. So look for it in the next few days. Sorry about that.

    The Alerts would be setup in conjunction with Operators. This is a topic unto itself. In the 'SQL Server Agent Core Components' section I referenced a number of SQL Server Books Online articles that I think will answer your question. The primary article would be SQL Server Agent Service.

    Please let me know if this answers your question.

    Thank you,

    Jeremy Kadlec

  • I've had this problem , where I would forget checking when transaction log backup ran last. I would check for failure of the job, but some one would have disabled the Xaction log backup for some reason and would have never turned it back. I finally wrote a script to send me alert if the Xaction Log backup had not run for more than 15min (Thats how often it is supposed to run). Here is the script hope this is useful


    Declare @i_CurDate int,

     @i_CurTime int,

     @i_MaxDt int,

     @i_MaxTime int

    --Get Last Run Date time of Xaction Log Backup

    select @i_MaxDt = max(run_Date),

     @i_MaxTime = max(run_time)

     from sysjobhistory where job_id in (

    select job_id from sysjobs where name ='BACKUPNAME')

    and run_status=1

    --Convert Current Date and Time to Int format

    select @i_CurDate = Cast(convert(Char(20),getdate() ,112) as int)

    select @i_CurTime =(Cast(left(convert(varchar(10),getdate() ,108),2) as int)* 100 +

     Cast(substring(convert(varchar(10),getdate() ,108),4,2) as int)

    &nbsp *100 + Cast(right(convert(varchar(10),getdate() ,108),2) as int)

    --If Difference between Last Run and Current Time > 1500(15 minutes), Then Notify

    If (@i_CurDate-@i_MaxDt) + (@i_CurTime-@i_MaxTime) > 1500


     Exec master..xp_sendmail @recipients = 'recipients' ,@message = 'Check XXX Transaction Log backup', @subject = 'Check XXX Transaction Log backup',@no_output = 'TRUE'






  • Hi Guys:

    Backups and Disaster Recovery are key ingredients to any business. Costs do a play part, but i think the costs of a disaster far outweigh the cost of the solution. Over the years, I have tried many solutions, but most have failed their tests on providing a seamless backup or restore with compression and encryption technology that delivers. I want to sleep at nite!!!!

    Then I was introduced at a managed online service from What a shocker!!! This service works seamlessly with all alerts received by mail..No need to tinker with backups anymore, it works seamlessly and it stores your data in their data centers securely, and it restores without any issues..

    Aha!!! most DBAs or IT heads will say i want to own the process, but why do you do that when a managed service provides all the benefits of disk based backup through a subscription, and the technology works..Let the experts do the work!!!!

    I am avid user of Evault, take me up on this, I guarantee you, you will never want to change to any other solution...




  • the link to 15CriticalBackupRestoreAlerts.sql script is still not working.  Any ideas where this can be obtained from?  Cheers.

  • The link is still dead and probably so is this thread. SSC fail 🙁

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply