Last months T-SQL Tuesday party dealt with PowerShell. I debated on whether or not to post a topic, but in the end I didn’t feel like I should post something that I had not really ever tried before so I spent a few weeks going through some basic power shell scripting and what I ended up with was a way to check my databases from a list of servers I have contained in a text file; along with retrieving failed job history based on the same server listing.

I was impressed enough that I started to use some of this technology on my on-call days where I have automated reports dumped out into excel waiting for me when I get in for review.

While there are many variations of this out there I researched and then put some of my own twists into it when creating the scripts; I only plan today to show one that I use the SMO assembly to retrieve basic database information.

The Script

First I want to open excel:

#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)

Now that excel is open I can begin to retrieve the list of databases from the text file and create all the header information:

#Counter variable for rows
$intRow = 1

#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in get-content “C:\Server\Servers.txt”)
{

#Create column headers
$Sheet.Cells.Item($intRow,1) = “INSTANCE NAME:”
$Sheet.Cells.Item($intRow,2) = $instance
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,2).Font.Bold = $True

$intRow++

$Sheet.Cells.Item($intRow,1) = “DATABASE NAME”
$Sheet.Cells.Item($intRow,2) = “OWNER”
$Sheet.Cells.Item($intRow,3) = “AUTOSHRINK”
$Sheet.Cells.Item($intRow,4) = “RECOVERY MODEL”
$Sheet.Cells.Item($intRow,5) = “LAST DATABASE BACKUP”
$Sheet.Cells.Item($intRow,6) = “SIZE (MB)”
$Sheet.Cells.Item($intRow,7) = “SPACE AVAILABLE (MB)”

#Format the column headers
for ($col = 1; $col –le 7; $col++)
{
$Sheet.Cells.Item($intRow,$col).Font.Bold = $True
$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
}

$intRow++

Now the fun part; time to get all the information and watch your excel file go:

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null

# Create an SMO connection to the instance
$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $instance

$dbs = $s.Databases

#$dbs | SELECT Name, Owner, AutoShrink, RecoveryModel, Last Database Backup, Size, SpaceAvailable

#Formatting using Excel

ForEach ($db in $dbs)
{

#Divide the value of SpaceAvailable by 1KB
$dbSpaceAvailable = $db.SpaceAvailable/1KB

#Format the results to a number with three decimal places
$dbSpaceAvailable = “{0:N3}” -f $dbSpaceAvailable

$Sheet.Cells.Item($intRow, 1) = $db.Name
$Sheet.Cells.Item($intRow, 2) = $db.Owner

#Change the background color of the Cell depending on the AutoShrink property value
if ($db.AutoShrink -eq “True”)
{
$fgColor = 3
}
else
{
$fgColor = 0
}

$Sheet.Cells.Item($intRow, 3) = $db.AutoShrink
$Sheet.Cells.item($intRow, 3).Interior.ColorIndex = $fgColor
if ($db.RecoveryModel -eq “1”)
{
$Sheet.Cells.Item($intRow, 4) = “FULL”
}
elseif ($db.RecoveryModel -eq “3”)
{
$Sheet.Cells.Item($intRow, 4) = “SIMPLE”
}
elseif ($db.RecoveryModel -eq “2”)
{
$Sheet.Cells.Item($intRow, 4) = “BULK-LOGGED”
}
if ($db.LastBackupDate -eq “12:00:00 AM”)
{
$Sheet.Cells.Item($intRow, 5) = “Never”
}
else
{
$Sheet.Cells.Item($intRow, 5) = $db.LastBackupDate
}
$Sheet.Cells.Item($intRow, 6) = “{0:N3}” -f $db.Size

#Change the background color of the Cell depending on the SpaceAvailable property value
if ($dbSpaceAvailable -lt 1.00)
{
$fgColor = 3
}
else
{
$fgColor = 0
}

$Sheet.Cells.Item($intRow, 7) = $dbSpaceAvailable
$Sheet.Cells.item($intRow, 7).Interior.ColorIndex = $fgColor

$intRow ++

}

$intRow ++

}

$Sheet.UsedRange.EntireColumn.AutoFit()
cls

Conclusion:

I added and removed some various objects in the information I was pulling back. There are a lot of great topics out there that show you what the objects are and many various books that provide the syntax in order to accomplish many things utilizing PowerShell.

I was not happy to miss the party last month but going through the exercise of diving in and doing some things with PowerShell I am glad I did. I look forward to doing some more involved work with it as I move forward in the future ~ I’ve only begun to scratch the surface!