Memo to self - listing databases status in Powershell

Very quick and dirty. Subject to evolve onto. Also sends email with a status report as well as an email in case any of the databases is not in the "Normal" state.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 

$Server = new-object ('Microsoft.SqlServer.Management.Smo.Server') '.'

$stats = "";
$stats += "Databases status for server {0}:`n" -f $Server.Name

foreach ($db in $Server.Databases)
{
    $stats += "{0, 25} `tStatus: {1}`n" -f $db.Name, $db.Status
    if ($db.Status.toString() -ne "Normal")
    {
        ## Notify by email
        $sc = new-object Net.Mail.SmtpClient -arg $EmailServer
        $sc.Send($EmailFrom, $EmailTo, "Error: Databases check on server {0} identified db [{1}] that is in {2} state at {3}" -f $Server.Name, $db.Name, $db.Status, $(Get-Date), $stats)
    }
}

write-Output $stats

## Notify by email
$sc = new-object Net.Mail.SmtpClient -arg $EmailServer
$sc.Send($EmailFrom, $EmailTo, $EmailSubjectPrefix + " Databases check was executed at " + $(Get-Date), $stats)

Technorati Tags: powershell, sql server