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
The below code will add "SQL Server Version Names to the above code" PowerShell: SQL Server Version Info Script

PowerShell Script: SQL Server Version Information

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 = @('maps')

#ForEach-Loop start
$serverlist | ForEach-Object{
  $indsserver = $_
  $Server = New-Object Microsoft.SQLServer.Management.Smo.Server $indsserver

  $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
  $CutString = ($versionString).Substring(0,2)
  $proDuctlvl = ($Server.Information.Properties | ?{$_.Name -like 'ProductLevel'}).Value
  $isClustered = ($Server.Information.Properties | ?{$_.Name -like 'IsClustered'}).Value

  Write-Progress -Activity "Finding Server $($netName)" `
    -Status "Progress:" `
    -PercentComplete (($Counter/$serverlist.Count)*100)
  
  $Counter ++

  [pscustomobject]@{
    ServerName = $netName
    BuildNumber = $buildnumber
    Product = $product
    Edition = $Edition
    Type = switch($CutString){
      "16" {'SQL Server 2022'}
      "15" {'SQL Server 2019'}
      "14" {'SQL Server 2017'}
      "13" {'SQL Server 2016'}
      "12" {'SQL Server 2014'}
      "11" {'SQL Server 2012'}
      "10" {'SQL Server 2008 R2'}
    }
    VersionString = $versionString
    ProductLevel = $proDuctlvl
    IsClustered = $isClustered
  }
}|Format-Table -Wrap

#Export-Csv -Path 'C:\Temp\ServerVersionInfo_11182021.csv' -NoTypeInformation

Comments

Post a Comment

Popular posts from this blog

Always On FailOver Events

Image
We can run below PowerShell and SQL Server commands when we want to find ou when the failover has happened. cls   Get-winEvent -ComputerName   ListerNameHere   -filterHashTable @{logname = 'Microsoft-Windows-FailoverClustering/Operational' ; id = 1641 } |   Where-Object { $_ . Timecreated -like '*05/09/2020*' } | Format-Table -AutoSize -Wrap <# 35201-->timeout alert run this by changing -LogName to System 41-->41 is power shutdown run this in system #> clear Get-EventLog -LogName Application -After ( Get-Date ) . AddHours( -48 ) | Where-Object { $_ . EventID -in ( 1100 , 1074 , 35201,41,1561,7024,1135,7032,1146 )} | Format-Table -AutoSize -Wrap cls  Get-EventLog ` -LogName System |Where-Object{$_.EventID -in (1054,1053,1055,1138,1141,1142,5140,5142,1559,1560,1563,1588,1068,5144,1562,5143,5168,1564,1177)}|Format-Table -AutoSize -Wrap EXEC sp_readerrorlog 0,1,'lease worker' EXEC sp_readerrorlog 0,1,'...
Read more

The transaction log for database is full due to 'OLDEST_PAGE'

When I see the 'OLDEST_PAGE' in log_reuse_wait_desc column and not allowing to me to shrink the log file of database I followed the below steps 1) When you see 'OLDEST_PAGE' run CHECKPOINT on that particular database 2) Take the Transaction log backup of that particular database 3) Try to shrink the log file of database These steps worked for me when I encountered this issue in my environment.
Read more

SSAS Cube or Database backup with Powershell command "Backup-AsDatabase"

Image
Run the full code from Load assembly section. # Load the assembly #[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | Out-Null # Create the server object $server = New-Object Microsoft.AnalysisServices.Server $server . Connect ( "localhost" ) # Or your SSAS instance name #Date Converion with minutes and seconds $date = ( get-date ).ToString( "MM_dd_yyyy_hh_mm_ss" ) $Server = New-Object Microsoft.AnalysisServices.Server $Server .Connect( "MAPS\TEST" ) $dbservers = $Server .Databases| Select-Object Name,ParentServer $dbservers | ForEach -Object{ $inddb = $_ #The "Backup-AsDatabase" is especially for SSAS cube backup and downloaded from "SQLServer" powershell module Backup-ASDatabase -BackupFile "E:\MAPS_TEST\SSAS_BACKUP\NEW\ $( $ inddb.Name) _ $( $ date) .abf" -Name $inddb .Name -Verbose -Server $inddb .ParentServer Write-Host "SSAS cube backup [ $( $ i...
Read more