x

Send email after the backup is successful

I have created a job named email and I have 3 steps in this job:

  1. Step 1 BACKUP DATABASE [msdb] TO DISK = N'E:\Backup\backup_filename' WITH RETAINDAYS = 1, INIT , NOUNLOAD , NAME = N'Test', SKIP , STATS = 10, NOFORMAT In the advanced tab On success Go to step:[2] Email Notification Success On Failure action Go to Step [3] Email Notification Failure

Step 1 is successful and the backup is created in E:\Backup\backup_filename

  1. Email notification Success EXEC msdb.dbo.sp_send_dbmail @recipients = 'you@your.server',@subject='Backup Succesful', @body = 'bla bla bla with the file name here, 'E:\Backup\backup_filename', @body_format = 'text'; END In advanced tab On Success action Quit the job reporting success On failure Quit the job reporting failure.

  2. Email Notification Failure EXEC msdb.dbo.sp_send_dbmail @recipients = 'you@your.server',@subject='Backup Unsuccesful', @body = 'bla bla bla with the file name here, 'E:\Backup\backup_filename', @body_format = 'text'; END In the advanced tab On success Quit the job reporting failure an failure Quit the job reporting failure

So my issue is step 1 is successful and it never proceeds to step 2 and 3 so it cannot send email. How do I solve this issues.

Thank you

Chitra

more ▼

asked Feb 22, 2010 at 03:50 PM in Default

avatar image

Chitra
148 17 17 21

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Look at the Job History. See if there are any failures reported. How do you know it goes to step 2? Try starting the job at Step 2 to confirm that there is not problem with the email step. Also look at the event view of that machine to see if anything pertinent was logged there.

more ▼

answered Feb 22, 2010 at 04:40 PM

avatar image

TG
1.8k 3 5

(comments are locked)
10|1200 characters needed characters left

I can see that you had quite a lot of issues with this and even though this is an old post, I found myself in the same situation as you did, after having been through the same scenario of calling a stored procedure to send my notification e-mails.

After quite some time of trying to "fix" this error, although from my side I considered everything to be ok, I decided to look for alternatives.

After searching for quite a while I discovered that I can work around these errors I was getting by using some third party tools for managing my backup jobs. After using quite a number of online tools, I settled with one that allowed me to just set up my notifications for my backup tasks in less than 10 seconds and it has worked flawlessly ever since.

I am really happy with the tool I found and here's just a short snippet of how easy it is to get notified about the status of your job: http://sqlbak.com/blog/sql-server-backup-email-notification/

more ▼

answered Sep 28, 2014 at 04:52 PM

avatar image

radu_bak
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x16

asked: Feb 22, 2010 at 03:50 PM

Seen: 3742 times

Last Updated: Sep 28, 2014 at 04:52 PM

Copyright 2016 Redgate Software. Privacy Policy