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.

Check database using SMO

For a long time I have checked the databases by DBCC CHECKDB, but I would like to check the database in PowerShell with SMO to include the check in a more general maintenance script with additional features.

The check can be done with the SMO Database class method CheckTables().
[string]$ServerName = 'DNDBA01.dn.lan'
$Server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName
$Server.Databases | ForEach-Object {
"{0:s}  Checking database [$($_.Name)]..." -f $([System.DateTime]::Now)
$_.CheckTables([Microsoft.SqlServer.Management.Smo.RepairType]::None, [Microsoft.SqlServer.Management.Smo.RepairOptions]::AllErrorMessages)
}

All errormessages are included to help the administrator if a check fail. Also it can be used for analytics.

Additional features to the script can be considered after reading the discussion by Cindy Gross.

A nice introduction to checking database integrity with SMO is
"Getting Started with SMO in SQL 2005 - Integrity Checks" by Jasper Smith.

Red-Gate SQL Compare with PowerShell

I would like to compare the structure of two databases as in development vs production.
The tool SQL Compare from Red-Gate I have used on a daily basis, and I want similar result without the GUI, so that I can automate a comparison.
In the SQL Toolbelt there are beside SQL Compare a SQL Comparison SDK, but it is a version behind SQL Compare. One thing I would miss is the possibility to compare with a backup or a scriptfile.
By looking into the C# examples of the SDK and the assemblies with Visual Studio Object Browser, this (very) simple example gives a usable result. Please consider the example as a technical sprint, not a final solution.

# Generate path for Red-Gate SQL Compare assemblies
$SqlComparePath = "\Red Gate\SQL Compare 9"
# True if the OS is 64 bit (x64)
if([IntPtr]::Size -eq 8) { $SqlComparePath = ${env:ProgramFiles(x86)} + $SqlComparePath }
else { $SqlComparePath = ${env:ProgramFiles} + $SqlComparePath }
# Add Red-Gate Compare assembly
Add-Type -Path "$SqlComparePath\RedGate.SQLCompare.Engine.dll"
# Register staging database
[string]$stagingServerName = 'SQLDEV42.sqladmin.lan'
[string]$stagingDbName = 'sqladmin_repository'
$stagingConnectionProperties = New-Object RedGate.SQLCompare.Engine.ConnectionProperties $stagingServerName, $stagingDbName
$stagingDb = New-Object RedGate.SQLCompare.Engine.Database
$stagingDb.Register( $stagingConnectionProperties, [RedGate.SQLCompare.Engine.Options]::Default )
# Register Production database
[string]$prodServerName = 'SQLPRD42.sqladmin.lan'
[string]$prodDbName = 'sqladmin_repository'
$prodConnectionProperties = New-Object RedGate.SQLCompare.Engine.ConnectionProperties $prodServerName, $prodDbName
$prodDb = New-Object RedGate.SQLCompare.Engine.Database
$prodDb.Register( $prodConnectionProperties, [RedGate.SQLCompare.Engine.Options]::Default )
# Compare databases. Result in RedGate.SQLCompare.Engine.Difference object
$stagingDb.CompareWith( $prodDb, [RedGate.SQLCompare.Engine.Options]::Default ) |
Format-Table -Property Type,DatabaseObjectType,Name -AutoSize

The result is something like this:
 Type DatabaseObjectType Name
 ---- ------------------ ----
Equal              Table [sqladmin].[collation]
Equal              Table [sqladmin].[computer]
Equal              Table [sqladmin].[database]
Equal              Table [sqladmin].[database_history]
Equal              Table [sqladmin].[department]
Equal              Table [sqladmin].[edition]
Equal              Table [sqladmin].[environment]
Equal              Table [sqladmin].[file]
Equal              Table [sqladmin].[filegroup]
Equal              Table [sqladmin].[security]
Equal              Table [sqladmin].[ssdb]
Equal              Table [sqladmin].[version]
Equal               Role public
Equal               Role sqlanchor_user
Equal               Role db_owner
Equal               Role db_accessadmin
Equal               Role db_securityadmin
Equal               Role db_ddladmin
Equal               Role db_backupoperator
Equal               Role db_datareader
Equal               Role db_datawriter
Equal               Role db_denydatareader
Equal               Role db_denydatawriter
Equal             Schema dbo
Equal             Schema guest
Equal             Schema INFORMATION_SCHEMA
Equal             Schema sys
Equal             Schema sqladmin
Equal             Schema sqlanchor
Equal             Schema db_owner
Equal             Schema db_accessadmin
Equal             Schema db_securityadmin
Equal             Schema db_ddladmin
Equal             Schema db_backupoperator
Equal             Schema db_datareader
Equal             Schema db_datawriter
Equal             Schema db_denydatareader
Equal             Schema db_denydatawriter
Equal               View [sqladmin].[v_computer]
Equal               View [sqladmin].[v_database]
Equal               View [sqladmin].[v_ssdb]
Equal               View [sqladmin].[v_ssdb_full]
Equal    StoredProcedure [sqlanchor].[ssdb_version-get]
Equal    StoredProcedure [sqlanchor].[environment-get]
Equal    StoredProcedure [sqlanchor].[environment_detail-get]
Equal    StoredProcedure [sqlanchor].[computer_summary-get]
...

Before you use the assemblies, take a look at the Red-Gate license agreement.