PowerShell - Get SQL Server Info

ben himself's picture
$assemblylist = 
"Microsoft.SqlServer.Management.Common",
"Microsoft.SqlServer.Smo",
"Microsoft.SqlServer.Dmf ",
"Microsoft.SqlServer.Instapi ",
"Microsoft.SqlServer.SqlWmiManagement ",
"Microsoft.SqlServer.ConnectionInfo ",
"Microsoft.SqlServer.SmoExtended ",
"Microsoft.SqlServer.SqlTDiagM ",
"Microsoft.SqlServer.SString ",
"Microsoft.SqlServer.Management.RegisteredServers ",
"Microsoft.SqlServer.Management.Sdk.Sfc ",
"Microsoft.SqlServer.SqlEnum ",
"Microsoft.SqlServer.RegSvrEnum ",
"Microsoft.SqlServer.WmiEnum ",
"Microsoft.SqlServer.ServiceBrokerEnum ",
"Microsoft.SqlServer.ConnectionInfoExtended ",
"Microsoft.SqlServer.Management.Collector ",
"Microsoft.SqlServer.Management.CollectorEnum",
"Microsoft.SqlServer.Management.Dac",
"Microsoft.SqlServer.Management.DacEnum",
"Microsoft.SqlServer.Management.Utility"
 
 
foreach ($asm in $assemblylist)
{
    $asm = [Reflection.Assembly]::LoadWithPartialName($asm)
}
 
$computers = Get-Content -Path C:\scripts\servers.txt
# OR list servers as part of script
$computers = "Server1", "Server2", "Server3", "etc..."
  
foreach($computer in $computers){
 
    if(pingServer $computer -eq 1){
        try{
            $svr = new-object ('Microsoft.SqlServer.Management.Smo.Server')  $computer
            $svr | select Name, OSVersion, InstanceName, IsClustered,
                    Edition, 
                    BuildNumber, 
                    Product, 
                    ProductLevel, 
                    Version, 
                    Processors, 
                    PhysicalMemory, 
                    DefaultFile, 
                    DefaultLog, 
                    MasterDBPath, 
                    MasterDBLogPath, 
                    BackupDirectory, 
                    ServiceAccount | Where-Object {$_.Processors -gt 0} | Format-Table -Wrap
 
        }catch{
            Write-Host $computer Not a SQL box.
        }
 
    }else{
        Write-Host $computer failed ping test.
    }
}
 


Tags: 

Categories: