Script to find sql server version on multiple instances using Power shell

Script to find sql server version on multiple instances using Power shell

Now days I am doing lot of work over creating inventory where I have to collect lot of info from multiple servers and practically its nether it  possible to go on each and every server to extract same  nor I can connect multiple sql instance for doing same.

The only way to avoid this manual work is some script which can take server name input from a list and execute on all servers and of course give result in some readable format.

Now days I am learning powershell , and Article of Ronald Dameron helped me to get this done quickly

So here is the plan

1.      Create  one text file name “X:\Monitoring_Automation\Servers.txt”

             è    This file will keep list of servers on which I want to execute script or get version of sql instance.

2.      Create one PS1 file name  “X:\Monitoring_Automation\Version.PS1”

             è    This file will keep powershell  code to generate sql version, code is given below

/**************************** Code Start *************************************/

## Get SQL Version installed on multiple servers

 $start = get-date

 write-host "Start: "  $start
  [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

## Specify path of output file

 $FilePath = "X:\Monitoring_Automation\"

 $OutFile = Join-Path -path $FilePath -childPath ("SQLVersions_" + (get-date).toString('yyyyMMdd_hhmmtt') + ".log")

## Output file name will be like “X:\Monitoring_Automation\ SQLVersions_20130906_1248AM.log” 

  # Below is loop to generate version of each server

  @(foreach ($svr in get-content "X:\Monitoring_Automation\Servers.txt")
  {
      $s = New-Object "Microsoft.SqlServer.Management.Smo.Server" $svr
      $s | select Name, Version
   })  | export-csv -noType $OutFile
   $end = get-date   
  write-host "End: "  $end

## You can extract output in excel using delimiters

/******************************* Code End ************************************/

3.      Create one batch file named “X:\Monitoring_Automation\Generateversion.bat”

            è    From this file we will call our power shell code file  ie PS1 file, Code will be as below

/*************************Code Start ********************************/

                Powershell.exe   X:\Monitoring_Automation\version.ps1
            /*************************Code End *************************************/

Once we create all 3 fill double click on batch file, You wil get output in log file create in our local folder only “X:\Monitoring_Automation\ SQLVersions_20130906_1248AM.log”

Thanks to Ronald Damelon for sharing his knowledge in public blog , This was one of easiest way to find sql server version on multiple servers.

Note : SQL query to find windows version using registry