Send alert if DB backup not taken or job hasn't been run.

Question

Send alert if DB backup not taken or job hasn't been run.

2005_DBA

Mr or Mrs. 500

Points: 524
More actions
July 26, 2010 at 5:39 pm

#238129

Hi Expert,
I want to make sure that my DB backup is taken every night.
We have third party Scheduler tool which run backup job for us and not SQL server. This tool will run T-SQL commannd enclosed in job.
Is there anyway I can get an alert if job hasn't been run due to any reason. I mean I want to query backup history table and send an email if there is no backup in last 2 hours for any database.
Your help will be greatly appreciated. Thanks in advance.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Fraggle-805517 SSCrazy Eights Points: 8907 More actions · Answer 1

This will give you a good start. Include the sp_sendmail if the backups haven't occured in the time frame you wanted and sql server will generate you a very nice email if the backup didn't occur assuming that your software actually records the backups in the msdb database. If not, then I would get with the software vendor and ask how to find this information.

http://www.mssqltips.com/tip.asp?tip=1251

Fraggle

2005_DBA Mr or Mrs. 500 Points: 524 More actions · Answer 2

Hi Fraggle,

Thanks for your reply. I know that we can query this table to get the backup detail but not quite sure how to integrate db send mail with the query so that it send us an email if backup is not there.

any idea on how to integrate this two will help me lot.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

2005_DBA (7/26/2010)
but not quite sure how to integrate db send mail with the query so that it send us an email if backup is not there.

Is the database mail configured on your system ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

2005_DBA Mr or Mrs. 500 Points: 524 More actions · Answer 4

2005_DBA

Mr or Mrs. 500

Points: 524

July 27, 2010 at 12:13 am

#1198546

yes Bhuvnesh DB mail is configure on my server.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

Then you can use sp_send_dbmail.and use the query parameter

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

sachnam SSCrazy Points: 2317 More actions · Answer 6

Hi dba_2005,

So far i understood your query you want to get an email if any of your job fails.

For this you should do the following assuming you are using 2005 or above

1. Create a database mail profile using database mail option under management option.

2. Test your profile by sending a test mail.

3. Create an operator put your profile name in the e-mail name text box. if you want you can test it with some testing job.

4. Once it is tested , i am sure it will work fine. select your backup job and click properties -> select notification tab and select the E-mail alert option and select your operator as created in step 3.

I am sure this will resolve your problem, even if you want you can set a netsend but it is not recommended in production enviornment,

Regards,

Sachin Sharma

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 7

You should be able to query for those dbs that have not had a backup in xx time. Then attach that result set to an email.

Or run the query twice. Once to see if there are rows (IF EXISTS) and if there are, run it again and send the email.

2005_DBA Mr or Mrs. 500 Points: 524 More actions · Answer 8

Thanks Steve,

If EXISTS is the command I was looking for.

Thanks everyone for your time and sharing knowledge. Keep it up.