Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Backup Monitoring and Reporting Expand / Collapse
Author
Message
Posted Wednesday, June 3, 2009 11:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:57 AM
Points: 259, Visits: 714
Comments posted to this topic are about the item Backup Monitoring and Reporting


Post #728403
Posted Thursday, June 4, 2009 8:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 10:15 AM
Points: 47, Visits: 796
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!
Post #728978
Posted Thursday, June 4, 2009 10:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
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
Post #729110
Posted Thursday, June 4, 2009 10:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, April 20, 2014 9:14 AM
Points: 358, Visits: 177
Good article, Backup Monitoring and Reporting

There is a mistake in it:

msdb.baclkupset -> msdb.backupset


Thank You


http://www.arcns.no.sapo.pt
Post #729130
Posted Friday, June 5, 2009 1:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 22, 2014 2:32 AM
Points: 1,260, Visits: 3,423
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/
Post #729445
Posted Friday, June 5, 2009 1:48 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 4:42 AM
Points: 425, Visits: 554
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.



Post #729458
Posted Friday, June 5, 2009 5:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:57 AM
Points: 259, Visits: 714
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.



Post #729578
Posted Friday, June 5, 2009 5:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:57 AM
Points: 259, Visits: 714
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.



Post #729593
Posted Friday, September 4, 2009 2:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 9, 2010 4:10 AM
Points: 70, Visits: 117
Really really helpful and well written - thank you very much.
Post #782775
Posted Tuesday, September 8, 2009 10:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 12, 2011 1:21 PM
Points: 3, Visits: 83
Nice article, thanks
Post #784393
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse