Automated email

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?

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,

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

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.

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.