How to setup Backup Report to be emailed?

Question

How to setup Backup Report to be emailed?

zeo

SSC Eights!

Points: 914
More actions
May 1, 2006 at 4:13 pm

#152947

How would I create a backup report showing the status(success/failure) of a full/transaction log backup for every database a on Sqlserver? I have many Sqlservers I need to do this for.
I need to set this up to be emailed daily.

Viewing 13 posts - 1 through 12 (of 12 total)

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

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

There's a number of third-party tools which will do this for you. That's easiest. Either that or just configure yourself as an operator and set the backup job to notify you on job failure (assuming you've got agent mail configured).

If you want to roll your own there's a couple of techniques you can do. IMHO the easiest, assuming that you've got each backup as its own job or own job step (i.e. you're not doing all backups in a single job step), is to query msdb.dbo.sysjobstephistory joined with msdb.dbo.sysjobs for the information (you'd want to convert run_date to datetime for anything in the last day). If all the jobs are single-job/single-step like in a maintenance plan, you won't know if individual backups failed or succeeded.

Then you'll have to take the output of that query and feed it into your mail transport of choice (SQL Agent Mail, XPSMTP or whatever else you use).

If you're using SQL Server 2005, you could easily modify the code I had in my article to do this (shameless plug):

http://qa.sqlservercentral.com/columnists/aingold/workingaround2005maintenanceplans.asp

zeo SSC Eights! Points: 914 More actions · Answer 2

I would like to get the following report daily for all of Sqlservers we take backups for.

Let me give an example of an email report I am looking for:

SqlServer Database Backup Type Status Start Time End Time

---------------------------------------------------------------------------------------------------

TestSql01 DataInfo Full Success 5/1/2006 22:00 5/1/2006 22:09

TestSql01 DataInfo Transaction Log Success 5/1/2006 22:15 5/1/2006 22:30

ProdSql01 Transreport Full Failure 5/1/2006 18:00 5/1/2006 18:17

ProdSql01 Transreport Transaction log Success 5/1/2006 19:00 5/1/2006 19:19

ProdSql01 Vtek Full Success 5/1/2006 17:30 5/1/2006 17:41

ProdSql01 Vtek Transaction Log Failure 5/1/2006 18:30 5/1/2006 18:38

Nali SSCommitted Points: 1525 More actions · Answer 3

One thing you can do is set up a Multi-Server administration system, having one server designated as a Master (MSX), and the rest as Targets (TSX). Then, you create a maintenance job or plan to apply to all the Target servers. You will then be able to centrally monitor and report on those jobs/maintenance plans. Look up "Multiserver Administration" on bol.

zeo SSC Eights! Points: 914 More actions · Answer 4

So it means I can create the report by pulling the data I need from one sqlserver?

How do I get the data I need for the information specified in the report?

Nali SSCommitted Points: 1525 More actions · Answer 5

You should be able to get the info from the Master.

I believe that within the maintenance plan you can designate the table you want to populate with your data, then the report, from this table.

zeo SSC Eights! Points: 914 More actions · Answer 6

ok please specify which tables/views hold the information I need for the following?

SQLServer Name

Database Name

Backup Type

Backup Status

Start Time

End Time

Nali SSCommitted Points: 1525 More actions · Answer 7

Here are the fields it currently has:

Plan Name:

Server Name:

Activity:

Status:

End date:

Start date:

Duration:

Error Number:

Message:

The table on the Master server is msdb.dbo.sysdbmaintplan_history

zeo SSC Eights! Points: 914 More actions · Answer 8

How do I query the name of the sqlserver from the database?

Nali SSCommitted Points: 1525 More actions · Answer 9

I'm not sure I understand the question...To get the name of a sql server, you can use the

SELECT @@SERVERNAME

zeo SSC Eights! Points: 914 More actions · Answer 10

How do I find information on "Multiserver Administration" on bol? What is bol?

Nali SSCommitted Points: 1525 More actions · Answer 11

BOL is Books On Line. While you're in SQL Enterprise Manager, hit the F1 key on your keyboard, or click on help. Then, in the search or index window, type in "multiserver administration". See also http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_automate_7ir2.asp

Let me know if you need more info.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 12

I query MSDB sysjobs and sysjobhistory tables on different servers from one server (from one workstation) using OPENROWSET with composing a query string with connection information dynamically for each server name and populating the temp table with the result. I do keep server names in one of the tables in my administration database. Then I use SQL server 2005 Database Mail to email results.

Regards,Yelena Varsha