Send email after the backup is successful

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

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

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

asked Feb 22 '10 at 03:50 PM in Default

Chitra
148 ● 10 ● 15 ● 19

(comments are locked)

1 answer:

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.

answered Feb 22 '10 at 04:40 PM

TG
1.8k ● 1 ● 3

Hi TG,

I know that step1 is successful because there are 3 steps to this job and step one has status as success and fails in step two. The file is also created in E:Backup. I am trying this for few days and do not know the right answer so any bit of help on this issue will be of big help.

Thank you

Chitra

Feb 23 '10 at 12:09 PM Chitra

Error message of Step 2
Job 'Email' : Step 2, 'Email Notification Success' : Began Executing 2010-02-22 12:42:39

Msg 102, Sev 15, State 1, Line 1 : Incorrect syntax near 'END'. [SQLSTATE 42000]

Feb 23 '10 at 12:12 PM Chitra

Post the actual code for step 2. You pseudo code has an END with no corresponding BEGIN. Perhaps that's the problem.

Feb 23 '10 at 12:32 PM TG

Hi TG,

EXEC msdb.dbo.spsenddbmail @recipients = 'you@your.server',@subject='Backup Succesful', @body = 'bla bla bla with the file name here, 'E:Backupbackupfilename', @bodyformat = 'text'; END

Feb 23 '10 at 12:55 PM Chitra

EXEC msdb.dbo.spsenddbmail @recipients = 'you@your.server',@subject='Backup Succesful', @body = 'bla bla bla with the file name here: "E:Backupbackupfilename"', @bodyformat = 'text'

Feb 23 '10 at 07:30 PM TG

(comments are locked)