Script to find sql server version on
multiple instances using Power shell
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
{
$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 *************************************/
Note : SQL query to find windows version using registry
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
Ronald help does work well :-)
ReplyDeletegood code
ReplyDelete