September 7, 2010 at 10:43 am
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.
September 7, 2010 at 10:54 am
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 7, 2010 at 11:07 am
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
September 7, 2010 at 11:13 am
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
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 7, 2010 at 11:23 am
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
September 7, 2010 at 11:33 am
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://qa.sqlservercentral.com/scripts/31662/
http://qa.sqlservercentral.com/scripts/31277/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 7, 2010 at 11:37 am
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.
September 8, 2010 at 11:01 am
September 8, 2010 at 1:40 pm
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply