Friday, 7 August 2009

Get-SQLServerVersion.ps1

  1. <# 
  2. .SYNOPSIS 
  3.     This script displays the versions of SQL Server running on a system 
  4. .DESCRIPTION 
  5.     This script uses WMI to get the SQLServiceAdvancedProperty class from 
  6.     the ComputerManagement namespace to print out the versions. This  
  7.     script is an adaptation of the VBS script on MSDN. 
  8. .NOTES 
  9.     File Name  : Get-SQLServerVersion.ps1 
  10.     Author     : Thomas Lee - tfl@psp.co.uk 
  11.     Requires   : PowerShell V2 CTP3 
  12. .LINK 
  13.     This script posted to: 
  14.         http://www.pshscripts.blogspot.com 
  15.     MSDN Sample posted at: 
  16.         http://msdn.microsoft.com/en-us/library/ms186353.aspx 
  17. .EXAMPLE 
  18.     PSH [C:\foo]: .\Get-SQLVersion.ps1' 
  19.     You are running the following versions of SQL: 
  20.     Service Name            Version 
  21.     ------------            ------- 
  22.     MSSQL$MICROSOFT##SSEE   9.3.4035.00 
  23.     MSSQL$SQLEXPRESS        9.3.4035.00 
  24. #> 
  25.  
  26. ## 
  27. # Start of script 
  28. ## 
  29.  
  30. # Get the versions of SQL from WMI 
  31. $Versions = Get-WmiObject -Namespace root\Microsoft\SQLServer\computerManagement -Class SqlServiceAdvancedProperty | where {$_.SqlServiceType -eq 1 -and $_.PropertyName -eq "VERSION"
  32.  
  33. # Now display results 
  34. "You are running the following versions of SQL:" 
  35. "Service Name            Version" 
  36. "------------            -------" 
  37. foreach ($version in $versions) { 
  38. "{0} `t{1}" -f $version.servicename,$version.propertystrvalue 
  39. #End of script 
Technorati Tags: ,,,

5 comments:

cmille19 said...

The WMI provider for SQL Server only works against SQL 2005 or higher. Another option that will support SQL Server 2000 is to use SMO

param ($sqlserver)
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
$server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $sqlserver | select name, @{name='Version';Expression={$_.information.version}}

martha said...

I recently came accross your blog and have been reading along. I thought I would leave my first comment. I dont know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.


Susan

http://8080proxy.com

Thomas said...

Thanks for stopping by Martha and thanks for your kind words. Suggestions etc always welcome. I hope you enjoy the ride!

Andre Galitsky said...

Hi Thomas, nice blog! You've got a lot of useful scripts here.

How do you format your script code so it looks so nice? I post some Powershell scripts on my Wordpress blog (http://www.sharepointnomad.com), but the text wraps around and it's difficult to copy & paste...

Andre

Thomas said...

Andre,

Thank you for your kind words. Regarding the syntax highlighting, I use the page: http://www.thecomplex.plus.com/highlighter.html, It works OK, although the syntax colouring is based on early Monad, and does not support the V2 features, Longer scripts sometimes hangs (in which case I break the script up and cut/paste each part manually into Live Writer.

Hope this helps

Thomas