Tuesday, September 29, 2009

PowerShell: Quick SQL Server Version Check

I have to keep track of our SQL Server version inventory.  The goal is to reduce the SQL Server 2000 population as fast as possible.


The following PowerShell script will produce a csv file containing the database server name and the version of SQL Server it's running.


1:  ## Get SQL Version installed on multiple servers ##
2:  ## ./sqlver.ps1
3:  $start = get-date
4:  write-host "Start: "  $start
5:   
6:  [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
7:   
8:  $FilePath = "C:\Output"
9:  $OutFile = Join-Path -path $FilePath -childPath ("SQLVersions_" + (get-date).toString('yyyyMMdd_hhmmtt') + ".log")
10:   
11:  # Version inventory
12:  @(foreach ($svr in get-content "C:\Input\AllLOBServers.txt")
13:  {
14:      $s = New-Object "Microsoft.SqlServer.Management.Smo.Server" $svr
15:      $s | select Name, Version
16:   
17:  })  | export-csv -noType $OutFile
18:   
19:  $end = get-date    
20:  write-host "End: "  $end
21:   

The csv file format gives you the option of using Excel or importing to a database table for review.


PowerShell is a great way to automate simple tasks that become tedious when you need to execute that task on hundreds of servers.