AKN518778

Monitor_AKN518778 (UnitMonitor)

SQL Server Database Backupset is marked as Damaged

Knowledge Base article:

Element properties:

Source Code:

<UnitMonitor ID="Monitor_AKN518778" Comment="SupportTopic=TBD;VersionNumber=1.0.0.0;" Accessibility="Public" Enabled="true" Target="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine" ParentMonitorID="Health!System.Health.ConfigurationState" Remotable="true" Priority="Normal" TypeID="KnowledgeServicesLibrary!Microsoft.KnowledgeServices.Library.PowerShellMonitorEx" ConfirmDelivery="true">

  <Category>Alert</Category>

  <AlertSettings AlertMessage="MonitorMessagec8540356160b4f16869fa35b33851099">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Data/Context/Property[@Name='DamagedDatabaseBackupInfo']$</AlertParameter1>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="Success" MonitorTypeStateID="Success" HealthState="Success"/>

    <OperationalState ID="Error" MonitorTypeStateID="Error" HealthState="Error"/>

  </OperationalStates>

  <Configuration>

    <ScriptName>AKN518778.ps1</ScriptName>

    <Parameters>

      <Parameter>

        <Name>ConnectionString</Name>

        <Value>$Target/Property[Type="MicrosoftSQLServerLibrary!Microsoft.SQLServer.DBEngine"]/ConnectionString$</Value>

      </Parameter>

    </Parameters>

    <ScriptBody><Script>





param($ConnectionString)



$ErrorActionPreference = "Stop"



# Set up the arguments

$scriptargs = new-object psobject

$scriptargs | add-member NoteProperty "ConnectionString" $ConnectionString



# Set up the output

$global:scriptoutput = new-object psobject

$scriptoutput | add-member NoteProperty "HasIssue" $false

$scriptoutput | add-member NoteProperty "DamagedDatabaseBackupInfo" ""

#----------------------------------------------------- 

# MAIN CODE SECTION

#----------------------------------------------------- 



# Environment



$scriptenv = New-Object PSObject

$scriptenv | Add-Member NoteProperty "ErrorFlag" $false



# Helper functions



# Function to get the connection string to the target Database

Function Get-DBConnectionString(){

    $connStrBulider = New-Object -TypeName System.Data.SqlClient.SqlConnectionStringBuilder

            $ConnStrBulider["Data Source"] = $scriptargs.ConnectionString

            $ConnStrBulider["Database"] = "master"

            $ConnStrBulider["Trusted_Connection"] = "SSPI"

    $connStrBulider.ConnectionString

}



#Private function to format XML data for UI display the databases

Function Format-XMLData($LogItems){

	$formatString = ""

	$xmlTemplate = "&lt;row&gt;&lt;DatabaseName&gt;{0}&lt;/DatabaseName&gt;&lt;BackupName&gt;{1}&lt;/BackupName&gt;&lt;BackupDate&gt;{2}&lt;/BackupDate&gt;&lt;/row&gt;"

	

	If($LogItems -ne $null -and ([Array]$LogItems).Count -ne 0)

	{

		([Array]$LogItems) | ForEach-Object{

			$formatString += ($xmlTemplate -f $_.DatabaseName,$_.BackupName,$_.BackupDate)

		}

	}

	$formatString

}



# Function to get the backup damaged databases

Function Get-DamagedBackupedDBs(){

	$damagedDatabaseBackups = @()

	

	# Create SqlConnection object

	$oConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection

	$oConnection.ConnectionString = Get-DBConnectionString

	$oConnection.open()



	# Create SqlCommand object, define command text, and set the connection

	$oSQLCommand = New-Object System.Data.SqlClient.SqlCommand

	

    # Use the query below to get the databases which has damaged backup

	$oSQLCommand.CommandText = 	"WITH BackupInfo AS 

								( 

									SELECT database_name AS [DatabaseName], name AS [BackupName], is_damaged AS [BackupStatus]

									,backup_start_date AS [BackupDate] 

									,ROW_NUMBER() OVER(PARTITION BY database_name ORDER BY backup_start_date DESC) AS BackupIDForDB 

									FROM msdb..backupset 

								) 

								SELECT DatabaseName,BackupName,BackupDate 

								FROM Backupinfo 

								WHERE BackupIDForDB = 1 and BackupStatus = 1"

	

	$oSQLCommand.Connection = $oConnection



	# Create SqlDataReader

	$oSQLDataReader = $oSQLCommand.ExecuteReader()



	If ($oSQLDataReader.HasRows){

 		While ($oSQLDataReader.Read()){

			$dbDamageBackupInfo = New-Object -TypeName PSObject

			$dbDamageBackupInfo | Add-Member NoteProperty "DatabaseName" $null 

			$dbDamageBackupInfo | Add-Member NoteProperty "BackupName" $null

			$dbDamageBackupInfo | Add-Member NoteProperty "BackupDate" $null

			

			$dbDamageBackupInfo.DatabaseName 	= $oSQLDataReader["DatabaseName"]

			$dbDamageBackupInfo.BackupName 		= $oSQLDataReader["BackupName"]

			$dbDamageBackupInfo.BackupDate 		= $oSQLDataReader["BackupDate"]

			

			$damagedDatabaseBackups += $dbDamageBackupInfo

		}

	}

	

	# Close the data reader and the connection

	$oSQLDataReader.Close()

	$oConnection.Close()

	

	# return the damaged database backups

	$damagedDatabaseBackups

}



# Main function

Function AdvisorRule($scriptargs, $scriptoutput) { 

	# All parameters should be populated outside of the main function. 

	# The main function should only include the detection logic so that it can be easyly reused by the Atlanta authoring tool.

	

	trap [Exception] { 

		$scriptenv.ErrorFlag = $true

		continue

	}

	

	# Initialize parameters

	$scriptenv.ErrorFlag = $false

	

	$scriptoutput.HasIssue = $false

	$scriptoutput.DamagedDatabaseBackupInfo = $null 

	



	# Set parameter values

	$damagedDatabaseBackups = Get-DamagedBackupedDBs

	

	If($damagedDatabaseBackups){

		$scriptoutput.DamagedDatabaseBackupInfo = Format-XMLData $damagedDatabaseBackups

	}

	

	# Detection Logic

	If($damagedDatabaseBackups){

		If($scriptenv.ErrorFlag -ne $true){

			#Raise Alert

			$scriptoutput.HasIssue = $true

		}

	}

}

AdvisorRule $scriptargs $scriptoutput



# set the output

$mom = new-object -comobject "MOM.ScriptAPI"

$bag = $mom.CreatePropertyBag()



if ($scriptoutput.HasIssue -ne $null)

{

	$bag.AddValue("HasIssue", $scriptoutput.HasIssue)

}



if ($scriptoutput.DamagedDatabaseBackupInfo -ne $null)

{

	$bag.AddValue("DamagedDatabaseBackupInfo", $scriptoutput.DamagedDatabaseBackupInfo)

}



$bag



</Script></ScriptBody>

    <SnapIns/>

    <TimeoutSeconds>300</TimeoutSeconds>

    <Schedule>86397</Schedule>

    <ErrorExpression>

      <SimpleExpression>

        <ValueExpression>

          <XPathQuery Type="Boolean">Property[@Name='HasIssue']</XPathQuery>

        </ValueExpression>

        <Operator>Equal</Operator>

        <ValueExpression>

          <Value Type="Boolean">true</Value>

        </ValueExpression>

      </SimpleExpression>

    </ErrorExpression>

    <SuccessExpression>

      <Not>

        <Expression>

          <SimpleExpression>

            <ValueExpression>

              <XPathQuery Type="Boolean">Property[@Name='HasIssue']</XPathQuery>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="Boolean">true</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

      </Not>

    </SuccessExpression>

  </Configuration>

</UnitMonitor>