Post reply

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

  • 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.

  • July 26, 2010 at 9:34 pm

    #1198518

    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

  • July 26, 2010 at 11:29 pm

    #1198532

    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.

  • July 26, 2010 at 11:45 pm

    #1198541

    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;-)

  • July 27, 2010 at 12:13 am

    #1198546

    yes Bhuvnesh DB mail is configure on my server.

  • July 27, 2010 at 1:30 am

    #1198566

    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;-)

  • July 27, 2010 at 2:48 am

    #1198586

    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

  • July 27, 2010 at 9:36 am

    #1198795

    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.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • July 27, 2010 at 6:44 pm

    #1199048

    Thanks Steve,

    If EXISTS is the command I was looking for.

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

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

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