Home
  •  

AK85906

Monitor_AK85906 (UnitMonitor)

SQL Server database files and backup files exist on the same volume

Knowledge Base article:

External

http://go.microsoft.com/fwlink/?LinkId=199790

Element properties:

TargetMicrosoft.KnowledgeServices.SQLServer.Database
Parent MonitorSystem.Health.ConfigurationState
CategoryAlert
EnabledTrue
Alert GenerateTrue
Alert SeverityWarning
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.KnowledgeServices.Library.PowerShellMonitorEx
RemotableTrue
AccessibilityPublic
Alert Message
SQL Server database files and backup files exist on the same volume
<Details>
<Content>Backup files since the last full database backup (including the last database backup) and database files for the SQL Server database are located on the same volume. If this volume or drive encounters a problem, you might not have a valid backup to use in a disaster recovery procedure. This might result in a data loss situation. Review your backup scripts or jobs and change them to perform the backup to a volume or drive other than the one used for storing the database files. If you have only a single drive on this server, consider performing a backup to a remote server.
</Content>
<CollectedInformation>
<Info>
<Name>Affected Database(VolumeOfDBFiles,DBFilesPath,VolumeOfBackupFiles,BackupFilesPath)</Name>
<Value>{0}</Value>
</Info>
</CollectedInformation>
</Details>
RunAsDefault
CommentSupportTopic=TBD;VersionNumber=1.0.1.0;

Source Code:

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

  <Category>Alert</Category>

  <AlertSettings AlertMessage="MonitorMessage1bb5c182717b4cb5afa53ee328c10fbc">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

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

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

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

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

  </OperationalStates>

  <Configuration>

    <ScriptName>AK85906.ps1</ScriptName>

    <Parameters>

      <Parameter>

        <Name>ConnectionString</Name>

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

      </Parameter>

      <Parameter>

        <Name>StrDataBaseNameInput</Name>

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

      </Parameter>

    </Parameters>

    <ScriptBody><Script>





param($ConnectionString,$StrDataBaseNameInput)



$ErrorActionPreference = "Stop"



# Set up the arguments

$scriptargs = new-object psobject

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

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



# Set up the output

$global:scriptoutput = new-object psobject

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

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

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

# MAIN CODE SECTION

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



#region Environment



$scriptenv = New-Object System.Management.Automation.PSObject

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

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

$scriptenv | Add-Member NoteProperty "AffectedDatabasesXMLEnv" ""



#endregion



#Private method to create affected databases entity object

function NewAffectedDBColumn($volumeOfDBFiles,$dbFilesPhysicalName,$volumeOfBackupFiles,$backupFilesPhysicalName)

{

	$scriptCollectedInfo = New-Object System.Management.Automation.PSObject

	$scriptCollectedInfo | Add-Member NoteProperty "VolumeOfDBFilesDC" $volumeOfDBFiles

	$scriptCollectedInfo | Add-Member NoteProperty "DBFilesPhysicalNameDC" $dbFilesPhysicalName

	$scriptCollectedInfo | Add-Member NoteProperty "VolumeOfBackupFilesDC" $volumeOfBackupFiles

	$scriptCollectedInfo | Add-Member NoteProperty "BackupFilesPhysicalNameDC" $backupFilesPhysicalName



	$scriptCollectedInfo

}



#Private method to format XML data for UI display

function FormatXMLData([array]$arrayList)

{

	$xmlTemplate = "&lt;row&gt;&lt;VolumeOfDBFiles&gt;{0}&lt;/VolumeOfDBFiles&gt;&lt;DBFilesPhysicalName&gt;{1}&lt;/DBFilesPhysicalName&gt;&lt;VolumeOfBackupFiles&gt;{2}&lt;/VolumeOfBackupFiles&gt;&lt;BackupFilesPhysicalName&gt;{3}&lt;/BackupFilesPhysicalName&gt;&lt;/row&gt;"



	if($arrayList)

	{

		$arrayList | ForEach-Object{

			$scriptenv.AffectedDatabasesXMLEnv += ($xmlTemplate -f $_.VolumeOfDBFilesDC,$_.DBFilesPhysicalNameDC,$_.VolumeOfBackupFilesDC,$_.BackupFilesPhysicalNameDC)

		}

	}

}



function New-Type

{

	#Support PowerShell 1.0, Same as Add-Type of PS 2.0

	#http://huddledmasses.org/custom-icomparers-in-powershell-and-add-type-for-v1/

	param([string]$TypeDefinition,[string[]]$ReferencedAssemblies)



	## Obtains an ICodeCompiler from a CodeDomProvider class.

	$provider = New-Object Microsoft.CSharp.CSharpCodeProvider

	## Get the location for System.Management.Automation DLL

	$dllName = [PsObject].Assembly.Location

	## Configure the compiler parameters

	$compilerParameters = New-Object System.CodeDom.Compiler.CompilerParameters



	$assemblies = @("System.dll","System.Management.dll", $dllName)

	$compilerParameters.ReferencedAssemblies.AddRange($assemblies)

	if($ReferencedAssemblies) 

	{ 

	  $compilerParameters.ReferencedAssemblies.AddRange($ReferencedAssemblies) 

	}



	$compilerParameters.IncludeDebugInformation = $true

	$compilerParameters.GenerateInMemory = $true



	$provider.CompileAssemblyFromSource($compilerParameters, $TypeDefinition) | Out-Null

}



function FormatVolumeResults($volumeName,$diskDeviceID)

{

	$scriptInfo = New-Object System.Management.Automation.PSObject

	$scriptInfo | Add-Member NoteProperty "VolumeName" $volumeName

	$scriptInfo | Add-Member NoteProperty "DiskDeviceID" $diskDeviceID

	

	$scriptInfo

}



function AddReparsePointsType()

{

	$source = @'

	using System;

using System.IO;

using System.Management;

using System.Runtime.InteropServices;

using System.Text;



namespace ReparsePoints

{

    //Reference: http://www.codeproject.com/Articles/21202/Reparse-Points-in-Vista

    public class ReparsePoint

    {

        private const uint IO_REPARSE_TAG_MOUNT_POINT = 0xA0000003;

        private const uint IO_REPARSE_TAG_SYMLINK = 0xA000000C;

        private const UInt32 SE_PRIVILEGE_ENABLED = 0x00000002;

        private const string SE_BACKUP_NAME = "SeBackupPrivilege";

        private const uint FILE_FLAG_BACKUP_SEMANTICS = 0x02000000;

        private const uint FILE_FLAG_OPEN_REPARSE_POINT = 0x00200000;

        private const uint FILE_DEVICE_FILE_SYSTEM = 9;

        private const uint FILE_ANY_ACCESS = 0;

        private const uint METHOD_BUFFERED = 0;

        private const int MAXIMUM_REPARSE_DATA_BUFFER_SIZE = 16 * 1024;

        private const uint TOKEN_ADJUST_PRIVILEGES = 0x0020;

        private const int FSCTL_GET_REPARSE_POINT = 42;



        [StructLayout(LayoutKind.Sequential, CharSet = CharSet.Unicode)]

        private struct REPARSE_DATA_BUFFER

        {

            public uint ReparseTag;

            public short ReparseDataLength;

            public short Reserved;

            public short SubsNameOffset;

            public short SubsNameLength;

            public short PrintNameOffset;

            public short PrintNameLength;

            [MarshalAs(UnmanagedType.ByValArray, SizeConst = MAXIMUM_REPARSE_DATA_BUFFER_SIZE)]

            public char[] ReparseTarget;

        }



        [StructLayout(LayoutKind.Sequential)]

        private struct LUID

        {

            public UInt32 LowPart;

            public Int32 HighPart;

        }



        [StructLayout(LayoutKind.Sequential)]

        private struct LUID_AND_ATTRIBUTES

        {

            public LUID Luid;

            public UInt32 Attributes;

        }



        private struct TOKEN_PRIVILEGES

        {

            public UInt32 PrivilegeCount;

            [MarshalAs(UnmanagedType.ByValArray, SizeConst = 1)]

            public LUID_AND_ATTRIBUTES[] Privileges;

        }



        [DllImport("kernel32.dll", ExactSpelling = true, SetLastError = true, CharSet = CharSet.Auto)]

        [return: MarshalAs(UnmanagedType.Bool)]

        static extern bool DeviceIoControl(

            IntPtr hDevice,

            uint dwIoControlCode,

            IntPtr lpInBuffer,

            uint nInBufferSize,

            out REPARSE_DATA_BUFFER outBuffer,

            uint nOutBufferSize,

            out uint lpBytesReturned,

            IntPtr lpOverlapped);



        [DllImport("Kernel32.dll", SetLastError = true, CharSet = CharSet.Auto)]

        static extern IntPtr CreateFile(

            string fileName,

            [MarshalAs(UnmanagedType.U4)] FileAccess fileAccess,

            [MarshalAs(UnmanagedType.U4)] FileShare fileShare,

            int securityAttributes,

            [MarshalAs(UnmanagedType.U4)] FileMode creationDisposition,

            uint flags,

            IntPtr template);



        [DllImport("advapi32.dll", SetLastError = true)]

        [return: MarshalAs(UnmanagedType.Bool)]

        static extern bool OpenProcessToken(IntPtr ProcessHandle,

            UInt32 DesiredAccess, out IntPtr TokenHandle);



        [DllImport("kernel32.dll")]

        static extern IntPtr GetCurrentProcess();



        [DllImport("advapi32.dll", SetLastError = true, CharSet = CharSet.Auto)]

        [return: MarshalAs(UnmanagedType.Bool)]

        static extern bool LookupPrivilegeValue(string lpSystemName, string lpName,

            out LUID lpLuid);



        [DllImport("advapi32.dll", SetLastError = true)]

        [return: MarshalAs(UnmanagedType.Bool)]

        static extern bool AdjustTokenPrivileges(IntPtr TokenHandle,

            [MarshalAs(UnmanagedType.Bool)]bool DisableAllPrivileges,

            ref TOKEN_PRIVILEGES NewState,

            Int32 BufferLength,

            IntPtr PreviousState,

            IntPtr ReturnLength);



        [DllImport("kernel32.dll", SetLastError = true)]

        [return: MarshalAs(UnmanagedType.Bool)]

        static extern bool CloseHandle(IntPtr hObject);



        [DllImport("kernel32.dll", SetLastError = true)]

        static extern bool GetVolumeNameForVolumeMountPoint(string

           lpszVolumeMountPoint, [Out] StringBuilder lpszVolumeName,

           uint cchBufferLength);



        private static string normalisedTarget = string.Empty;

        private static string actualTarget = string.Empty;

        private static TagType tag = TagType.None;



        /// &lt;summary&gt;

        /// Takes a full path to a reparse point and finds the target.

        /// &lt;/summary&gt;

        /// &lt;param name="path"&gt;Full path of the reparse point&lt;/param&gt;

        public static void CheckReparsePoint(string path)

        {

            bool success;

            int lastError;

            // Apparently we need to have backup privileges

            IntPtr token;

            TOKEN_PRIVILEGES tokenPrivileges = new TOKEN_PRIVILEGES();

            tokenPrivileges.Privileges = new LUID_AND_ATTRIBUTES[1];

            success = OpenProcessToken(GetCurrentProcess(), TOKEN_ADJUST_PRIVILEGES, out token);

            lastError = Marshal.GetLastWin32Error();

            if (success)

            {

                success = LookupPrivilegeValue(null, SE_BACKUP_NAME, out tokenPrivileges.Privileges[0].Luid);			// null for local system

                lastError = Marshal.GetLastWin32Error();

                if (success)

                {

                    tokenPrivileges.PrivilegeCount = 1;

                    tokenPrivileges.Privileges[0].Attributes = SE_PRIVILEGE_ENABLED;

                    success = AdjustTokenPrivileges(token, false, ref tokenPrivileges, Marshal.SizeOf(tokenPrivileges), IntPtr.Zero, IntPtr.Zero);

                    lastError = Marshal.GetLastWin32Error();

                }

                CloseHandle(token);

            }



            if (success)

            {

                // Open the file and get its handle

                IntPtr handle = CreateFile(path, FileAccess.Read, FileShare.None, 0, FileMode.Open, FILE_FLAG_OPEN_REPARSE_POINT | FILE_FLAG_BACKUP_SEMANTICS, IntPtr.Zero);

                lastError = Marshal.GetLastWin32Error();

                if (handle.ToInt32() &gt;= 0)

                {

                    REPARSE_DATA_BUFFER buffer = new REPARSE_DATA_BUFFER();

                    // Make up the control code - see CTL_CODE on ntddk.h

                    uint controlCode = (FILE_DEVICE_FILE_SYSTEM &lt;&lt; 16) | (FILE_ANY_ACCESS &lt;&lt; 14) | (FSCTL_GET_REPARSE_POINT &lt;&lt; 2) | METHOD_BUFFERED;

                    uint bytesReturned;

                    success = DeviceIoControl(handle, controlCode, IntPtr.Zero, 0, out buffer, MAXIMUM_REPARSE_DATA_BUFFER_SIZE, out bytesReturned, IntPtr.Zero);

                    lastError = Marshal.GetLastWin32Error();

                    if (success)

                    {

                        string subsString = "";

                        string printString = "";

                        // Note that according to http://wesnerm.blogs.com/net_undocumented/2006/10/symbolic_links_.html

                        // Symbolic links store relative paths, while junctions use absolute paths

                        // however, they can in fact be either, and may or may not have a leading \.

                        if (buffer.ReparseTag == IO_REPARSE_TAG_SYMLINK)

                        {

                            // for some reason symlinks seem to have an extra two characters on the front

                            subsString = new string(buffer.ReparseTarget, (buffer.SubsNameOffset / 2 + 2), buffer.SubsNameLength / 2);

                            printString = new string(buffer.ReparseTarget, (buffer.PrintNameOffset / 2 + 2), buffer.PrintNameLength / 2);

                            tag = TagType.SymbolicLink;

                        }

                        else if (buffer.ReparseTag == IO_REPARSE_TAG_MOUNT_POINT)

                        {

                            // This could be a junction or a mounted drive - a mounted drive starts with "\\??\\Volume"

                            subsString = new string(buffer.ReparseTarget, buffer.SubsNameOffset / 2, buffer.SubsNameLength / 2);

                            printString = new string(buffer.ReparseTarget, buffer.PrintNameOffset / 2, buffer.PrintNameLength / 2);

                            tag = subsString.StartsWith(@"\??\Volume") ? TagType.MountPoint : TagType.JunctionPoint;

                        }



                        // the printstring should give us what we want

                        if (!string.IsNullOrEmpty(printString))

                        {

                            normalisedTarget = printString;

                        }

                        else

                        {

                            // if not we can use the substring with a bit of tweaking

                            normalisedTarget = subsString;

                            if (normalisedTarget.StartsWith(@"\??\"))

                            {

                                normalisedTarget = normalisedTarget.Substring(4);

                            }

                        }

                        actualTarget = normalisedTarget;

                        // Symlinks can be relative.

                        if (buffer.ReparseTag == IO_REPARSE_TAG_SYMLINK &amp;&amp; (normalisedTarget.Length &lt; 2 || normalisedTarget[1] != ':'))

                        {

                            // it's relative, we need to tack it onto the path

                            if (normalisedTarget[0] == '\\')

                            {

                                normalisedTarget = normalisedTarget.Substring(1);

                            }

                            if (path.EndsWith(@"\"))

                            {

                                path = path.Substring(0, path.Length - 1);

                            }

                            // Need to take the symlink name off the path

                            normalisedTarget = path.Substring(0, path.LastIndexOf('\\')) + @"\" + normalisedTarget;

                            // Note that if the symlink target path contains any ..s these are not normalised but returned as is.

                        }

                        // Remove any final slash for consistency

                        if (normalisedTarget.EndsWith("\\"))

                        {

                            normalisedTarget = normalisedTarget.Substring(0, normalisedTarget.Length - 1);

                        }

                    }

                    CloseHandle(handle);

                }

                else

                {

                    success = false;

                }

            }

        }



        public static string GetVolumeCaption(string mountPoint)

        {

            const int MaxVolumeNameLength = 100;

            string volumeCaption = string.Empty;



            StringBuilder volumeName = new StringBuilder(MaxVolumeNameLength);

            if (!GetVolumeNameForVolumeMountPoint(string.Format(@"{0}\", mountPoint), volumeName, (uint)MaxVolumeNameLength))

                return volumeCaption;



            ManagementObjectSearcher searcher =

                    new ManagementObjectSearcher("root\\CIMV2",

                    "SELECT * FROM Win32_Volume");



            foreach (ManagementObject queryObj in searcher.Get())

            {

                if (string.Compare(queryObj["DeviceID"].ToString(),

                    volumeName.ToString()) == 0)

                {

                    volumeCaption = queryObj["Caption"].ToString();

                }

            }



            return volumeCaption;

        }



        /// &lt;summary&gt;

        /// This returns the normalised target, ie. if the actual target is relative it has been made absolute

        /// Note that it is not fully normalised in that .s and ..s may still be included.

        /// &lt;/summary&gt;

        /// &lt;returns&gt;The normalised path&lt;/returns&gt;

        public override string ToString()

        {

            return normalisedTarget;

        }



        /// &lt;summary&gt;

        /// Gets the actual target string, before normalising

        /// &lt;/summary&gt;

        public static string Target

        {

            get

            {

                return actualTarget;

            }

        }



        /// &lt;summary&gt;

        /// Gets the tag

        /// &lt;/summary&gt;

        public static TagType Tag

        {

            get

            {

                return tag;

            }

        }

    }



    public enum TagType

    {

        None = 0,

        MountPoint = 1,

        SymbolicLink = 2,

        JunctionPoint = 3

    }

}

'@

	

	New-Type -TypeDefinition $source

}



function GetMPDriveCaptionByPath($path)

{

	[ReparsePoints.ReparsePoint]::CheckReparsePoint($path)

	if([ReparsePoints.ReparsePoint]::Tag -eq [ReparsePoints.TagType]::MountPoint)

	{

		[ReparsePoints.ReparsePoint]::GetVolumeCaption($path)

	}

}



function GetSubFolderListByPath($path)

{

	if(Test-Path $path)

	{

		$folders = @()

		$folders += $path.Split('\')

		

		#Recursive method to get each sub folders

		for($i = 0;$i -le $folders.Length -2;$i++)

		{

			$folders[$i+1] += $folders[$i] +"\" +$folders[$i+1]

			$folders[$i+1] = $folders[$i+1].Substring($folders[$i+1].IndexOf($folders[$i]))

		}

		

		$folders

	}

}



function IsExistMPForSpecifiedPath($path)

{

	[array]$folders = GetSubFolderListByPath $path

	

	if($folders)

	{

		for($i = 1; $i -ne $folders.Length; $i++)

		{

			if(GetMPDriveCaptionByPath $folders[$i])

			{

				return $true

			}

		}

	}

}



function GetDriveVolumeBySpecifiedPath($path)

{

	if(Test-Path $path)

	{

		if(IsExistMPForSpecifiedPath $path)

		{

			[array]$folders = GetSubFolderListByPath $path

			if($folders)

			{

				for($i = $folders.Length-1; $i -ne 0; $i--)

				{

					$driveVolume = GetMPDriveCaptionByPath $folders[$i]

					if(-not $driveVolume)

					{

						continue

					}

					

					return $driveVolume

				}

			}

		}

		

		return $path.Substring(0,$path.IndexOf("\")+1)

	}

}



function GetDBConnectionString()

{

	if($scriptargs.ConnectionString -and `

	$scriptargs.StrDataBaseNameInput)

	{

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

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

        $ConnStrBulider["Database"] = $scriptargs.StrDataBaseNameInput

        $ConnStrBulider["Trusted_Connection"] = "SSPI"

    

		$connStrBulider.ConnectionString

	}

}



function CheckDBAndBackupFiles()

{	

	$DBConnectionString = GetDBConnectionString

	if($DBConnectionString)

	{

		# Create SqlConnection object and define connection string 

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

		$oConnection.ConnectionString = $DBConnectionString

		$oConnection.Open()



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

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

		$oSQLCommand.CommandText = "SELECT [dbfiles].[physical_name] AS DBFilesPhysicalName,[mediafamily].[physical_device_name] AS BackupFilesPhysicalName FROM msdb.dbo.backupmediafamily [mediafamily] JOIN (SELECT [backupsets].media_set_id, [backupsets].database_name FROM msdb.dbo.backupset [backupsets] JOIN (SELECT TOP 1 [backupsets].media_set_id, [backupsets].database_name, [backupsets].backup_finish_date FROM msdb.dbo.backupset [backupsets] JOIN sys.databases [databases] ON [backupsets].[database_name] = [databases].[name] and [databases].[is_in_standby] = 0 AND [databases].[source_database_id] IS NULL AND ([databases].name = 'master' OR DATEDIFF ( ss, [databases].[create_date] , [backupsets].[database_creation_date] ) = 0 ) WHERE [databases].[database_id] = DB_ID() AND [backupsets].type = 'D' ORDER BY [backupsets].backup_finish_date DESC) AS [latest_backupset] ON [backupsets].[database_name] = [latest_backupset].[database_name] AND [backupsets].[backup_finish_date] &gt;= [latest_backupset].[backup_finish_date]) AS latest_backups ON [latest_backups].[media_set_id]=[mediafamily].[media_set_id] JOIN sys.databases ON [latest_backups].[database_name] = [databases].[name] JOIN sys.master_files [dbfiles] ON [dbfiles].[database_id] = [databases].[database_id] JOIN sys.database_mirroring [mirrors] ON [mirrors].[database_id] = [databases].[database_id] AND [mirrors].[mirroring_guid] IS NULL WHERE UPPER(SUBSTRING([dbfiles].[physical_name],1,2)) != '\\'" 

		$oSQLCommand.Connection = $oConnection

		

		# Execute Command 

		$reader = $oSQLCommand.ExecuteReader()

		$affectedDBArray = @()

		

		while($reader.Read())

		{

			$volumeOfDBFile = GetDriveVolumeBySpecifiedPath $reader["DBFilesPhysicalName"]

			$volumeOfBackupFile = GetDriveVolumeBySpecifiedPath $reader["BackupFilesPhysicalName"]

			

			#SQL Server database files and backup files exist on the same volume

			if(($volumeOfDBFile -and $volumeOfBackupFile) -and `

			($volumeOfDBFile -eq $volumeOfBackupFile))

			{

				$affectedDBArray += NewAffectedDBColumn $volumeOfDBFile $reader["DBFilesPhysicalName"] $volumeOfBackupFile $reader["BackupFilesPhysicalName"]

			}

		}

		

		if($affectedDBArray)

		{

			FormatXMLData $affectedDBArray

			$scriptenv.IsDBAffectedEnv = $true

		}



		# Close reader object

		$reader.Close()



		# Close SqlConnection object

		$oConnection.Close()

	}

}





# 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 easily reused by the Atlanta authoring tool.



	trap [Exception] {

		$scriptenv.RuntimeError = $true

      	continue

   	}

	

	# Initialize parameters

	$scriptoutput.HasIssue = $false

	$scriptoutput.StrAffectedDatabasesXMLOutput = ""

	

	AddReparsePointsType

	CheckDBAndBackupFiles

	if($scriptenv.IsDBAffectedEnv -eq $true)

	{

		if($scriptenv.RuntimeError -eq $false)

		{

			$scriptoutput.HasIssue = $true

			$scriptoutput.StrAffectedDatabasesXMLOutput = $scriptenv.AffectedDatabasesXMLEnv

		}

	}

}

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.StrAffectedDatabasesXMLOutput -ne $null)

{

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

}



$bag



</Script></ScriptBody>

    <SnapIns/>

    <TimeoutSeconds>300</TimeoutSeconds>

    <Schedule>86396</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>