Adding email notifications 1

[phil22046]

I have the following SQL backup command that runs every night. It sometimes fails and I wanted to add a notification email to "infosys"

BACKUP DATABASE [S030] TO DISK = N'\\Sh-case2\S030Backup\S030Full.bak' WITH INIT , NOUNLOAD , NAME = N'S030 backup FULL', NOSKIP , STATS = 10, DESCRIPTION = N'Full Backup', NOFORMAT

I tried adding -To "Infosys" and it told me that was a syntax error. what should I add to get the notification email?

 

[phil22046]

Since I got no response at all, I decided to adjust the maintenance plan to do a daily Full Backup. This allows me to specify under reporting, the email address I want the notification to go to.

 

[phil22046]

Is there a way to have a SQL backup command and include something for Email notifications in case of failure?

I don't want to use the Maintenance plan for the backup if I can avoid it.

 

[AlexCuse]

Write a stored proc to execute your backup, and use one of many methods to send the email if errors are encountered.

It sounds like you want to do this from t-sql, so I'd recommend you look up the stored procedure xp_sendmail in BOL. (or just click here)

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[phil22046]

Is there a return value that the backup command returns so that I can tell if the backup command succeeded or failed?

 

[AlexCuse]

I'd think that @@ERROR will be sufficient in this case.

Something like

if @@ERROR > 0
begin
--send email
end


Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom