Getting SQL Server Version, Edition, Service pack information from Windows Server with PowerShell

 The below query retrieves the SQL Server instance information from Windows Server through Powershell. For now, it will retrieve information only from one windows server, will modify soon the below code to retrieve the same info from multiple windows servers.



CLS
Import-Module sqlps -DisableNameChecking
$instances=Get-ChildItem -Path 'SQLSERVER:\SQL\WindowsServerNameHere'
$instances|
ForEach-Object{
$singinstance=$_
$singinstance|
Get-Member|Where-Object {($_.Name -ne "SystemMessages")-and($_.MemberType -eq "Property")}|
Select-Object Name,@{Name="Value";Expression={$singinstance.($_.Name)}}|
Where-Object{($_.Name -eq 'InstanceName')-or `
($_.Name -eq 'Name') `
-or($_.Name -eq 'URN') `
-or($_.Name -eq 'Edition') `
-or($_.Name -eq 'Version') `
-or($_.Name -eq 'State') `
-or($_.Name -eq 'ServiceStartMode') `
-or($_.Name -eq 'ServiceName') `
-or($_.Name -eq 'ServiceAccount') `
-or($_.Name -eq 'ServerType') `
-or($_.Name -eq 'productlevel') `
-or($_.Name -eq 'rootdirectory') `
-or($_.Name -eq 'ResourceVersion') `
-or($_.Name -eq 'IsClustered') `
-or($_.Name -eq 'IsHadrEnabled') `
-or($_.Name -eq 'ServiceInstanceID')
}|Format-Table -AutoSize -Force
}
cls
$Counter=0
#$CsvData = Import-CSV -path $InputFile 
#5:15 am est
Import-Module sqlps -DisableNameChecking
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SQLServer.Smo')|Out-Null
$serverlist = @('InstanceName1',
'InstanceName2',
'InstanceName3',
'InstanceName4'
)#ForEach-Loop start
$serverlist| ForEach-Object{
#$x = 1
#$perc=[math]::round($x/$serverlist.count)
$indsserver=$_
$Server=New-Object Microsoft.SQLServer.Management.Smo.Server $indsserver
#$Server.Information.Properties|Select-Object Name, Value
$netName = ($Server.Information.Properties| ?{$_.Name -like 'Netname'}).VAlue
$product = ($server.Information.Properties | ?{$_.Name -like 'Product'}).VAlue
$Edition = ($server.Information.Properties | ?{$_.Name -like 'Edition'}).Value
$buildnumber = ($Server.Information.Properties|?{$_.Name -like 'BuildNumber'}).Value
$versionString = ($Server.Information.Properties|?{$_.Name -like 'VersionString'}).Value
$proDuctlvl = ($Server.Information.Properties|?{$_.Name -like 'ProductLevel'}).Value
$isClustered = ($Server.Information.Properties|?{$_.Name -like 'IsClustered'}).Value
#Write-progress -id 1 -percent $perc -activity “Looking up $($netName)”
Write-Progress -Activity "Finding Server $($netName)" -Status "Progress:"  -PercentComplete (($Counter/$serverlist.Count)*100)
$Counter ++
[pscustomobject]@{
NetName=$netName
BuildNumber=$buildnumber
product=$product
Edition=$Edition
VersionString=$versionString
ProductLevel=$proDuctlvl
IsClustered=$isClustered
}
#ForEach-LoopEnds below
}|Format-Table -AutoSize
#Export-Csv -Path 'C:\Temp\ServerVersionInfo_11182021.csv' -NoTypeInformation

Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server