Posts

Showing posts from April, 2021

Getting SQL Server services info from multiple servers through Powershell

Image
Here the STATE 4 means "RUNNING" 1 Stopped. And the server names should be Windows Server names in which SQL Server instances are installed. cls #SQL Server 2014 $cmt1 = "ComputerManagement12" #sqlserver2016 $cmt2 = "ComputerManagement13" #sqlserver2017 $cmt3 = "ComputerManagement14" #SQL Server 2012 $cmt4 = "ComputerManagement11" #SQL Server 2008/2008 R2 $cmt5 = "ComputerManagement10" #provide only windows server names here $server = @ ( 'SERVER1' , 'SERVER2' , 'SERVER3' ) $server | ForEach -Object{ $singcmpt = $_ cd C : \windows\System32 $CmptMgmt =gwmi -ns 'root\Microsoft\SqlServer' __NAMESPACE -ComputerName $singcmpt | ? { $_ .name -match $cmt1 } |Select Name $CmptMgmt | ForEach -Object{ $singCmptMgmt = $_ if ( $singCmptMgmt = $cmt1 ) { Write-Host "$singcmpt is SQL Server 2014" -Verbose Get-CimInstance -ComputerName $singcmpt -Namespace "root/Microsoft/SqlS...

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 -e...