Get backup results in an email

Question

Get backup results in an email

pshaship

SSCertifiable

Points: 6125
More actions
September 7, 2010 at 10:43 am

#237966

Hi,
I trying to get the backup results in an email when the full backup job has finished
Like the below content should come to my email-id. Could please advice me..Thanks
Right Now I'm getting an email when the backup job finished using Database Mail.
(1 row(s) affected)
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 67360 pages for database 'Mydb', file 'Mydb_data' on file 1.
100 percent processed.
Processed 1 pages for database 'Mydb', file 'Mydb_log' on file 1.
BACKUP DATABASE successfully processed 67361 pages in 25.546 seconds (21.601 MB/sec).
The backup set on file 1 is valid.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 1

These items are messages. They aren't stored, but there is most of this info in backupset (http://technet.microsoft.com/en-us/library/ms186299.aspx)

You could mail that.

Note that mailing yourself positive results is likely a waste of work. You shouldn't need to scan this if the backup is successful. I would only notify myself on negative results.

pshaship SSCertifiable Points: 6125 More actions · Answer 2

Thanks Steve,

I just want to get an email like below:

The backup job is completed successfully and following databases are backed up. and

The backup job is failed and following databases are backed up and following databases are failed and the reason to failure.

Thanks

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 3

Then you don't want to get this from the backup job. You want to run a job that will check for this after all the databases have been backed up. You'd want to write a query that looks through history, and then attaches those results to the email

pshaship SSCertifiable Points: 6125 More actions · Answer 4

Then you don't want to get this from the backup job. You want to run a job that will check for this after all the databases have been backed up. You'd want to write a query that looks through history, and then attaches those results to the email

Can I have a sample script to get start with? I just started writing database maintenance scripts:hehe:

Thanks

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 5

This is fairly simple SQL. Query the backupset table (or another table) for the information you want. Go look in BOL at the columns and choose the data that will fit your need, limit it by date, write a SELECT.

Here are a few you can work with:

http://www.sqlservercentral.com/scripts/31662/

http://www.sqlservercentral.com/scripts/31277/

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 6

Even if the backup says successful you really don't know for sure until you can successfully restore it.

After you get through this first step you may want to consider an additional process where you automate the restore of the backup and then determinethe success/failure of your restore.

RS-332567 Newbie Points: 5 More actions · Answer 7

RS-332567

Newbie

Points: 5

September 8, 2010 at 11:01 am

#1218113

That is exactly what SQLBackupAndFTP[/url] does

Derrick Smith SSChampion Points: 10833 More actions · Answer 8

Just save the step output to a text file, like you should already be doing, and then create two steps for notification in the job, so it looks like this:

1) Run backup - If Success, go to step 2. If fail, go to step 3

2) Send success email, end job as Success.

3) Send failure email, attach error log using the @attachments parameter (plain text file easily viewable on a blackberry), and quit the job reporting failure.

I usually set up new backup jobs to do this, and then after a few days of running successfully, I'll take out step 2 and only get an email if it fails.