Backup Monitoring and Reporting

  • Comments posted to this topic are about the item Backup Monitoring and Reporting

  • Very nice! I would optimize some things in the coding for large installations with hundreds of backups going on every hour (logs etc..) though. Example would be to the exclusion table and the "AND d.name NOT IN (SELECT db_name FROM dbautility.dbo.db_exclude)". Could be replaced with a JOIN logic and get the NOT out of there.

    All in all I enjoyed reading it and I can see this benefiting quite a few DBAs out there. Thanks!

  • Chad - excellent. I'll be adding a link to this in all my DBA classes.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Good article, Backup Monitoring and Reporting

    There is a mistake in it:

    msdb.baclkupset -> msdb.backupset

    Thank You

    http://www.arcns.no.sapo.pt

  • Yea, very nice article! It is in my briefcase now! Thank you for sharing! 😉

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • When I get execute the jobs I get the following error by starting the powershell script:

    Exception calling "WriteToServer" with "1" argument(s): "Violation of PRIMARY K EY constraint 'PK_backupset_dim'. Cannot insert duplicate key in object 'dbo.ba ckupset_dim'. The statement has been terminated." At D:\CustomReports\backupAnalysis.ps1:59 char:28 + $bulkCopy.WriteToServer( <<<< $dataTable)Exception calling "WriteToServer" with "1" argument(s): "Violation of PRIMARY K EY constraint 'PK_backupset_dim'. Cannot insert duplicate key in object 'dbo.ba ckupset_dim'. The statement has been terminated." At D:\CustomReports\backupAnalysis.ps1:59 char:28 + $bulkCopy.WriteToServer( <<<< $dataTable). Process Exit Code 0. The step succeeded.

  • sanderstad (6/5/2009)


    When I get execute the jobs I get the following error by starting the powershell script:

    Exception calling "WriteToServer" with "1" argument(s): "Violation of PRIMARY K EY constraint 'PK_backupset_dim'. Cannot insert duplicate key in object 'dbo.ba ckupset_dim'. The statement has been terminated." At D:\CustomReports\backupAnalysis.ps1:59 char:28 + $bulkCopy.WriteToServer( <<<< $dataTable)Exception calling "WriteToServer" with "1" argument(s): "Violation of PRIMARY K EY constraint 'PK_backupset_dim'. Cannot insert duplicate key in object 'dbo.ba ckupset_dim'. The statement has been terminated." At D:\CustomReports\backupAnalysis.ps1:59 char:28 + $bulkCopy.WriteToServer( <<<< $dataTable). Process Exit Code 0. The step succeeded.

    Try running update_server_dim_sp this wil update the server_dim.max_backupset_id column. Also keep in mind the primary key is defined as server_name and backup_set_id. The server name value is obtained from @@servername, so its possible to have duplicate names of your SQL Servers in your environment. Check @@servername on all servers you are polling and verify there are no duplicates.

  • Rui Nogueira (6/4/2009)


    Good article, Backup Monitoring and Reporting

    There is a mistake in it:

    msdb.baclkupset -> msdb.backupset

    Thank You

    Thanks -- I made a few spelling and grammar corrections, it might take a few days to show up.

  • Really really helpful and well written - thank you very much.

  • Nice article, thanks

  • Hello Chad

    I've tried to change it so that it picks up the backups over the weekend but I've failed miserably!

    Could you advise what I need to do to modify this so that the report shows every day in the drop down, rather than just Monday-Friday. Thanks very much in advance

  • jackie.jones (10/6/2009)


    Hello Chad

    I've tried to change it so that it picks up the backups over the weekend but I've failed miserably!

    Could you advise what I need to do to modify this so that the report shows every day in the drop down, rather than just Monday-Friday. Thanks very much in advance

    You'll need to change the insert dt_dim logic. This is where the query takes into account your backup windows. I'm using Monday through Thursday midnight through the 6 AM the next day 30 hours and on Friday a 78 hours. These lines need to be changed:

    WHEN DATEPART(dw,SeqDate) BETWEEN 2 AND 5 THEN DATEADD(hh,30,SeqDate)

    WHEN DATEPART(dw,SeqDate) = 6 THEN DATEADD(hh,78,SeqDate)

    2 through 5 is Monday through Thursday

    6 is Saturday

    Does this make sense? If you want, post your backup window and schedule and I'll tweak the query and post it here.

  • Hello

    I think I did that bit right because when I looked in the table dt_dim where it had previously had Nulls for the weekends, it now has the dates correctly shown up until the end of this year, with no gaps for weekends.

    In the reports however, it still only shows the week days in the drop down. When I checked the query in the reports the problem seemed to be due to the link between dt_dim and backup_fact (by the dt_id) as the backup_fact contains gaps for the weekends presumably. E.G. the dt_id jumps from 40079 to 40082 and from 40086 to 40089 etc, etc), so I guess that's why it doesn't show in the drop down? I guessed therefore that I've missed something else to update.

    Thanks for your speedy reply too

    Jackie

  • jackie.jones (10/7/2009)


    Hello

    I think I did that bit right because when I looked in the table dt_dim where it had previously had Nulls for the weekends, it now has the dates correctly shown up until the end of this year, with no gaps for weekends.

    In the reports however, it still only shows the week days in the drop down. When I checked the query in the reports the problem seemed to be due to the link between dt_dim and backup_fact (by the dt_id) as the backup_fact contains gaps for the weekends presumably. E.G. the dt_id jumps from 40079 to 40082 and from 40086 to 40089 etc, etc), so I guess that's why it doesn't show in the drop down? I guessed therefore that I've missed something else to update.

    Thanks for your speedy reply too

    Jackie

    Looking at this little further, the insert_backup_fact_sp is also customized to my environment and checks dw (day of week) between 2 and 6 (Monday and Saturday). Remove/comment out this line:

    AND d.dw BETWEEN 2 AND 6

    Then truncate backup_fact and re-run inserver_backup_fact and update_backup_fact_sp

  • Somebody please help me .

    I dont have powershell on my server since its a Windows 2003 Server

    Is there alternateive procedures written for this.

    Kindly help

    Thanks

    Navendu

Viewing 15 posts - 1 through 15 (of 30 total)

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