Alert! Alert! Backup and Restore Baby!

Question

Post reply

Alert! Alert! Backup and Restore Baby!

jeremyk

SSC Enthusiast

Points: 117
More actions
December 28, 2003 at 12:00 am

#177386

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

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

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

mimorr SSCarpal Tunnel Points: 4850 More actions · Answer 1

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

jeremyk SSC Enthusiast Points: 117 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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

timcarper Old Hand Points: 344 More actions · Answer 6

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