Post reply

Simple backup status report.

  • April 29, 2004 at 8:59 am

    #86581

    Good morning all,

    We have many remote client sites (doctor's offices) that are running SQL Server 2000.  Most do not have an email server on the network.  None of the sites have a DBA or even any technical people.

    For these reasons I need to develop a process of monitoring success/failure of scheduled backups that a non-technical user can use.  I would like to create an icon on the desktop that the user can click on in the morning and see a simple report of last night's backups.  I don't want them going into Enterprise Manager.

    Has anyone been down this road before?  I would appreciate any suggestions!

    Thanks

    Jonathan

  • April 29, 2004 at 10:53 am

    #504711

    You can do what I do. I have a script that checks the flat files as well as the jobs and generates an HTML file that is stored on the server's disk. I then have a process that emails this file to users, using a new filename everyday, but you could easily write to the same file and have it shared to user's desktops.

    Working on an article with the detailed script now. Should be out in 2-3 weeks.

  • April 29, 2004 at 10:58 am

    #504712

    I wonder if you have access to those databases?  If you do you could write a script to connect to each dr.'s office and find just which job fail and have that send to you so you could start the job again.  We monitor close to 100 server with more then 1500 databases, each server has between 20 jobs to 200 jobs, I don't think I want to know which job finish.  Would you?  All I need to know is which job fails and if the job could be restart during working hour.

     

     

    mom

  • April 29, 2004 at 11:33 am

    #504719

    Thanks for your suggestions!

    Steve, would it be possible to share the script with me as it is now?   If not no problem.

    Jonathan

  • April 29, 2004 at 5:48 pm

    #504750

    Jonathan,

    Create a "Reporting" database on these machines to write out the job errors to. Then you can simply have the job insert a record into the logging table in the Reporting database if the job fails. Then it is a simple matter of querying the log table for the errors.




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • April 30, 2004 at 7:34 am

    #504820

    Thanks Gary,

    Can you give me an idea of how to have the job insert a row in the logging table only if the job fails? 

    Thansks again.

    Jonathan

  • April 30, 2004 at 8:40 am

    #504840

    In the job (each one) add a new step "Insert into reportdb.dbo.FailedJobHistory (Job, DayFailed, etc..) values 'JobName', getdate(), etc..   (of course use you own db name, table, columns etc, you can even do some error trapping / log reading and put the reasons the job failed in the table)

    then go to what was previously the last step of the job, click the advanced tab and change the on success actions to 'quit the job success'  - and the on failure action to 'go to the next step' (the step you created above)

    On the step you just created (with the insert statement) - set the advanced actions to quit reporting failure for both on success and on failure (you want the job to show a red x for failure because the only reason this step gets run is the job has already failed).

     

    bad news is you have to literally do this for every job - you might want to wait for Steve Jones' script.


    Thanks, and don't forget to Chuckle

  • April 30, 2004 at 11:27 am

    #504875

    Thanks Chuck! You beat me to it




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • April 30, 2004 at 12:43 pm

    #504880

    Thanks everyone for your help!

    Jonathan

  • April 30, 2004 at 4:17 pm

    #504917

     

    The msdb database has a table backupset that keeps information of every database backup irrespective of whether the backup ran from a SQL server job, manual or directly to the tape. In my case I have over 50 servers to manage. I define all these servers as a linked servers to my central monitoring server. I have couple of jobs running on my central server, that nightly connect to the 50 servers and collect data from the backupset table, then reformat and present in a user friendly format to a web site. I am planning on putting together an article on this setup.

     

    Database Name

    Last Full Bkup

    Last Diff Bkup

    Last Log Bkup

    Last Update Date

    master

    Apr 28 2004 11:29:59:000PM

    NULL

    NULL

    Apr 30 2004 4:00:15:070AM

    model

    Apr 25 2004 4:09:43:000AM

    Apr 29 2004 2:24:26:000AM

    NULL

    Apr 30 2004 4:00:15:070AM

    msdb

    Apr 25 2004 4:09:22:000AM

    Apr 29 2004 2:24:15:000AM

    NULL

    Apr 30 2004 4:00:15:070AM

    Partition_2001Q2

    Feb 12 2004 4:06:55:000PM

    Jan 21 2004 10:18:20:000AM

    NULL

    Apr 30 2004 4:00:15:070AM

    Partition_2001Q2E

    Apr 25 2004 4:09:18:000AM

    Apr 29 2004 2:24:10:000AM

    NULL

    Apr 30 2004 4:00:15:070AM

    Partition_2001Q3

    Feb 12 2004 4:04:39:000PM

    Jan 21 2004 10:18:15:000AM

    NULL

    Apr 30 2004 4:00:15:070AM

    Partition_2001Q3E

    Apr 25 2004 4:06:27:000AM

    Apr 29 2004 2:24:06:000AM

    NULL

    Apr 30 2004 4:00:15:070AM

     

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

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