PowerShell and SQL

When PowerShell was first released a few years back I wasn’t too excited. I didn’t use scripting very much, mainly for cleaning out old backup files from our archived storage. I thought that using VBScript would suffice for my limited means. But I started reading up on PowerShell V1, even tinkering around with it a bit. However learning PowerShell soon dropped down on my “Must Learn” technologies. There was just too many other things being introduced.

My mind didn’t change much when PowerShell V2 was in beta. Then Microsoft told us how PowerShell was going to be integrated into SQL Server 2008 (among other products) and PowerShell began moving back up my list. I’d read articled on MSDN and blogs by SQL experts, but my thinking never really changed. I figured that PowerShell was just one more way of doing things I already did in other ways.

One of the items on my “To Do” list has always been to document the servers I’m responsible for. I toyed with different methods of gathering the information, from running SSIS packages to a central repository to executing T-SQL queries in a Reporting Service report to manually (and tediously) typing the data into Word docs.

Then I read an article by Edwin Sarmiento on MSSQL Tips. In his article, he gave an example of using SMO inside PowerShell to write the information to an Excel spreadsheet. Finally the light bulb went on; I could use PowerShell to create my Run Book!

Now I’m not going to go into an in-depth explanation on PowerShell, or SMO. There’s ton’s of information available, and they explain much clearer than I can. I’m still learning myself. Check out Allen White (blog), Buck Woody (blog | twitter), Aaron Nelson (blog | twitter) and many others that I’m not mentioning here. I just wanted to share the method I’m beginning to use. It’s still evolving as I learn new tricks.

The basis of my script was the one used by Edwin Sarmiento in this article. I’ve added a few things of my own to make it more like what I want. For instance I add server information as well as database information. And I put each server onto a different worksheet. 

The first thing my script does is to load the SMO namespace. After that I use the Get-Credential cmdlet to get an account to connect to the servers (I won’t always be connecting with Windows Authentication), create an Excel object and get a list of servers from a text file. After that I make sure that there’s enough worksheets in the workbook for all servers listed. Since a new Excel workbook has 3 worksheets by default I start my loop at 4.

#Get list of servers 
$srvlist = @(get-content ".\SQL_Servers.txt")

#Counter variable for rows
$c = $srvlist.Count 
$intRow = 1

#Verify there's a sheet in the workbook for each server 
for ($i = 4; $i -le $c; $i++) 
{
$Workbook.Sheets.Add() 
}

Then it’s just a matter of looping through the servers and databases, writing the values to the workbooks and applying formatting. My final step is to rename each sheet.

$name = $instance -replace("\\", "-")
$Sheet.Name = $name

Here’s an example of what one of my sheets looks like…

…and here’s the whole script. For this example I stopped at 7 columns so it would all show on the screen without scrolling. But my final script will show more information about both the servers and the databases.

#region LoadAssemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
#endregion

#Get SQL account information
$credential = Get-Credential
$loginName = $credential.UserName -replace("\\","")   $password = $credential.Password

$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $False
$Workbook = $Excel.Workbooks.Add()

#Get list of servers 
$srvlist = @(get-content ".\SQL_Servers.txt")

#Counter variable for rows
$c = $srvlist.Count 
$intRow = 1

#Verify there's a sheet in the workbook for each server 
for ($i = 4; $i -le $c; $i++) 
{
$Workbook.Sheets.Add() 
}

$a = 1
#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in $srvlist) 
{
$Sheet = $Workbook.Worksheets.Item($a) 
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
$srv.ConnectionContext.LoginSecure = $false
$srv.ConnectionContext.set_Login($loginName)
$srv.ConnectionContext.set_SecurePassword($password)    
#set headers
$Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,2) = $instance
$Sheet.Cells.Item($intRow,2).Font.Bold = $True
$intRow += 1
$Sheet.Cells.Item($intRow,1) = "VERSION:"
$Sheet.Cells.Item($intRow,2) = "EDITION:"
$Sheet.Cells.Item($intRow,3) = "COLLATION:"
$Sheet.Cells.Item($intRow,4) = "OS VERSION:"
$Sheet.Cells.Item($intRow,5) = "PLATFORM:"
$Sheet.Cells.Item($intRow,6) = "PHYS MEM:"
$Sheet.Cells.Item($intRow,7) = "NUM CPU:"
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
}

#get values
$intRow += 1

$Sheet.Cells.Item($intRow,1) = $srv.Information.Version 
$Sheet.Cells.Item($intRow,2) = $srv.Information.Edition
$Sheet.Cells.Item($intRow,3) = $srv.Information.Collation
$Sheet.Cells.Item($intRow,4) = $srv.Information.OSVersion
$Sheet.Cells.Item($intRow,5) = $srv.Information.Platform
$Sheet.Cells.Item($intRow,6) = $srv.Information.PhysicalMemory
$Sheet.Cells.Item($intRow,7) = $srv.Information.Processors

$intRow += 2

$Sheet.Cells.Item($intRow,1) = "DATABASES"
$Sheet.Cells.Item($intRow,1).Font.Bold = $True

$intRow += 1

$Sheet.Cells.Item($intRow,1) = "DATABASE NAME"
$Sheet.Cells.Item($intRow,2) = "COLLATION"
$Sheet.Cells.Item($intRow,3) = "COMPATIBILITY LEVEL"
$Sheet.Cells.Item($intRow,4) = "AUTOSHRINK"
$Sheet.Cells.Item($intRow,5) = "RECOVERY MODEL"
$Sheet.Cells.Item($intRow,6) = "SIZE (MB)"
$Sheet.Cells.Item($intRow,7) = "SPACE AVAILABLE (MB)"
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 += 1

$dbs = $srv.Databases
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.Collation
$Sheet.Cells.Item($intRow, 3) = $db.CompatibilityLevel
#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, 4) = $db.AutoShrink 
$Sheet.Cells.item($intRow, 4).Interior.ColorIndex = $fgColor
$Sheet.Cells.Item($intRow, 5) = $db.RecoveryModel
$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 += 1

}
$Sheet.UsedRange.EntireColumn.AutoFit()
$name = $instance -replace("\\", "-")
$Sheet.Name = $name
$intRow = 1
$a += 1
}
#Save file and close Excel. 
$xlExcel8 = 56
$timeStamp = Get-Date -Format "yyyyMMdd_HH_mm"
$fileName = ".\ServerInfo_" + $timeStamp + ".xls"
$Workbook.SaveAs($fileName, $xlExcel8)
$Excel.Quit
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)

I’ve run this script using PowerShell V2 and Excel 2007 and the Excel 2010 beta with no problems. I don’t believe there’s anything specific to V2 in the script. But I do have a warning, though. If, while you run the script, you get impatient and click somewhere inside Excel, you’ll get an error and the script will stop (NOTE TO SELF: Add error handling!). The error is HRESULT 800ac472 and it’s an Excel error, not PowerShell.