Backup Monitoring and Reporting

Question

Backup Monitoring and Reporting

cmille19

SSCertifiable

Points: 5970
More actions
June 3, 2009 at 11:33 am

#201157

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

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

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

Ted Krueger Ten Centuries Points: 1171 More actions · Answer 1

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!

Paul Randal One Orange Chip Points: 29544 More actions · Answer 2

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

Rui Nogueira SSC Eights! Points: 810 More actions · Answer 3

Good article, Backup Monitoring and Reporting

There is a mistake in it:

msdb.baclkupset -> msdb.backupset

Thank You

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

Dugi SSCoach Points: 17998 More actions · Answer 4

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]

sqlstad SSCrazy Points: 2484 More actions · Answer 5

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.

cmille19 SSCertifiable Points: 5970 More actions · Answer 6

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.

cmille19 SSCertifiable Points: 5970 More actions · Answer 7

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.

jackie.jones SSC Eights! Points: 810 More actions · Answer 8

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

BJCharly SSC Enthusiast Points: 167 More actions · Answer 9

BJCharly

SSC Enthusiast

Points: 167

September 8, 2009 at 10:37 am

#1050639

Nice article, thanks

jackie.jones SSC Eights! Points: 810 More actions · Answer 10

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

cmille19 SSCertifiable Points: 5970 More actions · Answer 11

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.

jackie.jones SSC Eights! Points: 810 More actions · Answer 12

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

cmille19 SSCertifiable Points: 5970 More actions · Answer 13

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

navendu.mohanty Valued Member Points: 66 More actions · Answer 14

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