How to setup Backup Report to be emailed?

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

  • 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://www.sqlservercentral.com/columnists/aingold/workingaround2005maintenanceplans.asp

  • 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

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

  • 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?

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

  • 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

  • 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

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

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

    SELECT @@SERVERNAME

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

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

  • 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

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

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