SQL Server
sherweb news

Fun with PowerShell: How to get SQL Server information

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

  • twitter
  • linkedin
  • facebook
  • google
  • 0

TAGS / KEYWORDS

About James Kehr

Discussion

So, you're ready to deploy O365 using Remote Desktop

Fill out this small form so we can get in touch

Thank you for your interest! We’ll contact you soon.