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

Chitra gravatar image

Chitra
148 15 17 19

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

1 answer: 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

TG gravatar image

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, 2010 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, 2010 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, 2010 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:\Backup\backupfilename', @bodyformat = 'text'; END
Feb 23, 2010 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:\Backup\backupfilename"', @bodyformat = 'text'
Feb 23, 2010 at 07:30 PM TG
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x18

asked: Feb 22, 2010 at 03:50 PM

Seen: 2075 times

Last Updated: Feb 22, 2010 at 03:50 PM