x

Automated email

I have found a link which can send email to the desired email aliases: http://www.howtogeek.com/howto/database/sending-automated-job-email-notifications-in-sql-server-with-smtp/. However I want to add more funtions to this, it should also give me the names of the backup files, please advice the possibility of sending email message to the user with the backup file names.

Chitra

more ▼

asked Feb 11 '10 at 02:49 PM in Default

csaha gravatar image

csaha
45 3 4 4

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

7 answers: sort newest

You dont mention what version of SQL Server you are using but the link you provide shows a method for SQL 2000. SQL 2000 used an SMTP client profile (usually Outlook) to send email. This method was deprecated in SQL 2205 and since then the SQL Server has its own method to send emails - DB Mail. review the stored procedure sp_send_dbmail and how to configure DBMail.

What is the job doing that you want to send emails about backups?

more ▼

answered Feb 11 '10 at 05:00 PM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

I am using sqlserver 2005, so should I be using sp_send_dbmail and DBMail instead.
Feb 11 '10 at 06:14 PM csaha
DBMail is the name for the email 'facility' in SQL 2005. Once you have configured that via the wizard (in SSMS object explorer, expand Management and then right click Database Mail and select properties) then you execute sp_send_dbmail from any script to send an email. You have many parameters for the email. Type sp_send_dbmail in a query, highlight it and press F1 for details.
Feb 11 '10 at 06:24 PM Fatherjack ♦♦
SQL 2205? Cool! Where can I get that? ;)
Feb 12 '10 at 04:56 AM David Wimbush
+1 - to David. Looks like I've broken an NDA. oops!
Feb 12 '10 at 06:02 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

chitrarekha,

The only way I see, is to enable in the backup step (first step), the Output to the job history checkmark and the select an Output file.

then you can:

1- Attach file to the mail EXEC msdb.dbo.sp_send_dbmail @recipients = 'you@your.server', @subject='Backup Succesful', @body = 'bla bla bla with the file name here, *X:\BackUP\backup_filename* ', @file_attachment='ABSOLUTE PATH OF FILE YOU ESPECIFIED AS OUTPUT ', @body_format = 'text' ; END

2- Run query to job history table SELECT fields_you_like FROM msdb.dbo.[sysjobhistory] INNER JOIN msdb.dbo.[sysjobs] ON [sysjobhistory].[job_id] = [sysjobs].[job_id] WHERE sysjobs.name = 'Name of job';

use result of query as message body or use query as @query parameter of sp_send_dbmail

3- you can parse Output job file to an SQL table using bulk insert and do some string formatting for message body query, but its a more complicated solution.

Hope it works for you,

more ▼

answered Feb 22 '10 at 10:42 PM

JD gravatar image

JD
104 6 7 8

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

Hi JD,

I ran the above mentioned code and it runs great and I need to send this message to email aliases.

10 percent processed. 20 percent processed. 30 percent processed. 40 percent processed. 50 percent processed. 60 percent processed. 70 percent processed. 80 percent processed. 90 percent processed. Processed 33072 pages for database 'Play', file 'IMSA2' on file 1. 100 percent processed. Processed 1 pages for database 'Play', file 'IMSA2_log' on file 1. BACKUP DATABASE successfully processed 33073 pages in 16.634 seconds (16.287 MB/sec).

more ▼

answered Feb 22 '10 at 12:42 AM

csaha gravatar image

csaha
45 3 4 4

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

weard, I use this script for my own full backups and works fine,

BACKUP DATABASE [my_database] TO DISK = 'X:\BackUP\backup_filename' WITH RETAINDAYS = 1, INIT , NOUNLOAD , NAME = N'my_name_of_job', SKIP , STATS = 10, NOFORMAT

Notice that I have removed the asterisks(*) in the code.

more ▼

answered Feb 20 '10 at 12:16 PM

JD gravatar image

JD
104 6 7 8

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

Hi JD,

I tried executing your script, but it is not reading the backup file names. I have created the backup files in E:\BackupFull\AbraHRMS_Live*.bak. During the full backup it creates all the database folders by itself and names the backup files. I would like the backup script to read the backup file names and email all the file names.

more ▼

answered Feb 19 '10 at 05:22 PM

csaha gravatar image

csaha
45 3 4 4

(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:

x35

asked: Feb 11 '10 at 02:49 PM

Seen: 2030 times

Last Updated: Feb 11 '10 at 02:49 PM