Gallery.technet.microsoft.com



How to get 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 versXon 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 “C: \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.

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download