While I like the SQL Server PowerShell provider I have two issues with it.  First, it’s kind of limited, and second, it only installs with 2008 versions of SQL Server.  For these reasons I prefer to use the good old fashion SQL Server SMO namespace to do my dirty work.  That and we have a lot of SQL Server 2005 in our environment.

http://msdn.microsoft.com/en-us/library/ms162169.aspx

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.aspx

Digging around in the SQL Server SMO, Server namespace, I noticed there was this lovely “Information” property list and thought, “I bet I could do something pretty cool with that.”  Now when I say cool, I mean cool in the I’m-a-geek definition of the word, not in the I-was-cool-and-popular-in-high-school version of the word.  And so I wrote a nifty little program, which can easily be turned into a function, that pulls all your basic SQL Server information from a system.

Then I realized that not all servers have just one instance of SQL Server installed, so I modified it to find multiple instances and return an array of PowerShell objects for each instance.  That was actually the hardest part because SMO won’t, as far as I could find, report all the instances installed.  I ended up using a Win32_Service call for that, since each instance of SQL Server has to have an associated service it was the quick and easy way to do it.  Get-service would have been easier but there is no remote option in PowerShell 1.0.

With no arguments the script uses the local server.  Pass a server name and it will go all remote on you.  Some examples:

.\getInfo-SqlServer.ps1

Version  : SQL Server 2008
Edition  : Express Edition with Advanced Services (64-bit)
fullVer  : 10.0.2734.0
majVer   : 10
minVer   : 0
Build    : 2734
Arch     : NT x64
Level    : SP1
Root     : c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL
Instance : SQLEXPRESS

.\getInfo-SqlServer.ps1 SomeServer

Version  : SQL Server 2005
Edition  : Express Edition
fullVer  : 9.00.4035.00
majVer   : 9
minVer   : 0
Build    : 4035
Arch     : NT INTEL X86
Level    : SP3
Root     : C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL
Instance : SQLEXPRESS

Version  : SQL Server 2008
Edition  : Web Edition
fullVer  : 10.0.2531.0
majVer   : 10
minVer   : 0
Build    : 2531
Arch     : NT INTEL X86
Level    : SP1
Root     : D:\Program Files\Microsoft SQL Server\MSSQL10.WEB\MSSQL
Instance : WEB

Version  : SQL Server 2000
Edition  : Standard Edition
fullVer  : 8.00.2282
majVer   : 8
minVer   : 0
Build    : 2282
Arch     : NT INTEL X86
Level    : SP4
Root     : D:\Program Files\Microsoft SQL Server\MSSQL
Instance : MSSQLSERVER

Now without further wordiness I present to the Internet, getInfo-SqlServer.ps1.

Download: getInfo-SqlServer.ps1

#James Kehr
Get-Member $OW | ?{$_.title -eq "System Administrator"`
-and $_.certification -contains 'MCITP:SA 2008, MCSE 2000, MCDST, Network+, A+'}

New-Variable -name company -value 'ORCS Web, Inc.' -description ‘www.orcsweb.com | 1.888.313.9421’

EDIT: I made one minor correction in the code.  Since SQL Server 2008 R2 has a minor version of 5 I changed line 39 from "10 {if ($tempSqlInfo.minVer -eq 0 ) {" to "10 {if ($tempSqlInfo.minVer -lt 5 ) {" to take into account any minor version changes future versions of SQL Server 2008 may have.