Alert! Alert! Backup and Restore Baby!

  • jeremyk

    SSC Enthusiast

    Points: 117

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jkadlec/alertalertbackupandrestorebaby.asp

  • mimorr

    SSCarpal Tunnel

    Points: 4740

    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?

    Thanks,

    Michelle



    Michelle

  • jeremyk

    SSC Enthusiast

    Points: 117

    Michelle,

    I will talk to someone at SQLServerCentral.com 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

    jeremyk@edgewoodsolutions.com

    http://www.edgewoodsolutions.com

  • sreejiths

    Old Hand

    Points: 391

    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

    Begin

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

    End

     

     

    Thanks

    Sreejith

  • SUMARIA

    Valued Member

    Points: 52

    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 http://www.evault.com... 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...

    Thanks,

    Bhavesh

     

  • cathy spee-204109

    SSC Rookie

    Points: 28

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

  • timcarper

    SSC Veteran

    Points: 204

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

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

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