Backup filesize on collections of databases

This morning I had to find the total file size of backup files on a collection of databases for a given system.
Actually the answer can be generated by a single PowerShell statement :-)
$(ls MySystemName*.* | measure -s -pr Length).Sum / 1gb

The answer is the number of gigabytes given by a Double number.

A more readable version of the statement without aliases is
$(Get-ChildItem MySystemName*.* | Measure-Object -Sum -Property Length).Sum / 1GB

The statement is invoked with the location in the backup folder.

If a sum of sizes from more than one location the amounts can be taken from UNC paths and addeded up
($(ls '\\SERVER01.sqladmin.lan\Y$\SQL Server Backup\SystemOne*.bak' | measure -s -pr Length).Sum + $(ls '\\SERVER02.sqladmin.lan\Y$\SQL Server Backup\SystemOne*.bak'| measure -s -pr Length).Sum) / 1gb
In this example the default shares are used in the UNC path.

PowerShell WMI - The RPC server is unavailable

I have a ongoing task of refining an automated collection (instrumentation) of computers in the organisation running a SQL Server database installation.
Most of the data are collected by WMI and the automation is done by PowerShell.
The other day I ran into a challenge when the computer is available, but the rights are insufficient. The WMI call by the cmdlet Get-WmiObject er initialised as the computer exists, but the data request fails due to the lach of rights. Actually the error does not generate a exception that is caught by the PowerShell try-catch exception handling. It looks like by various fora that it is a remoting issue.

A quick workaround is to take a look at the $Error last element with a match, and filter out the error message with the cmdlet parameter "-ErrorAction SilentlyContinue".
I know that the errormessage contains "HRESULT: 0x800706BA" and a match on this will catch the error in a robust way.


$DebugPreference = 'continue'

$ComputerName = 'SQL30.test.dn.ext'


"{0:s}  `"$ComputerName`"." -f ([System.DateTime]::Now) | Write-Debug




$objComputer = New-Object System.Object

Add-Member -InputObject $objComputer -MemberType NoteProperty -Name Name -Value $ComputerName




$WQL = "SELECT Manufacturer,Model,TotalPhysicalMemory,NumberOfProcessors,SystemType FROM Win32_ComputerSystem"

try {

  $_ComputerSystem = Get-WmiObject -Query $WQL -ComputerName $ComputerName -ErrorAction SilentlyContinue

}

catch [System.UnauthorizedAccessException] {

  ":: ERROR: WMI access denied."

}




# The error "The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)" is not caught but still generates an error

if ($Error[0].Exception -match "HRESULT: 0x800706BA") {

  ":: ERROR: WMI COM (RPC) not available."

}




Add-Member -InputObject $objComputer -MemberType NoteProperty -Name Manufacturer -Value $_ComputerSystem.Manufacturer

Add-Member -InputObject $objComputer -MemberType NoteProperty -Name Model -Value $_ComputerSystem.Model

Add-Member -InputObject $objComputer -MemberType NoteProperty -Name MemoryInKb -Value $(if($hasWmiAccess) {($_wmi.TotalPhysicalMemory / 1KB)} else {$null})

Add-Member -InputObject $objComputer -MemberType NoteProperty -Name CpuCoreCount -Value $_ComputerSystem.NumberOfProcessors

Add-Member -InputObject $objComputer -MemberType NoteProperty -Name SystemType -Value $_ComputerSystem.SystemType




$objComputer




Looking at PowerShell exception handling

With PowerShell v2 we were given a more complete exception handling than the trap-handling in PowerShell v1.
This is a quick spike on throwing an exception, catching it and looking at the $Error variable.
Feel free to continue :-)
Clear-Host


$Error.Clear()


try {
  #throw "MyException"
  #throw [System.DivideByZeroException]
  #throw [IO.PathTooLongException]
  throw [System.Data.NoNullAllowedException]
}
catch {
  $_.CategoryInfo
}
finally {
  ":: Final"
}


if ($Error) {
  "{0:s}  Error in script execution. See log for details." -f $([System.DateTime]::Now)
}


The output of the script above is
Category   : OperationStopped
Activity   : 
Reason     : RuntimeException
TargetName : System.Data.NoNullAllowedException
TargetType : RuntimeType


:: Final
2012-01-02T22:15:20  Error in script execution. See log for details.

More details and links is in the article "Windows PowerShell Error Records" on MSDN Windows Dev Center.
Please notice that the returned objects are not of the System.Exception class but of the ErrorRecord class.