How to Check for The SQL Server Version Using Powershell

How do I check for the SQL Server Version using Powershell?

Invoke-Sqlcmd -Query "SELECT @@VERSION;" -QueryTimeout 3

http://msdn.microsoft.com/en-us/library/cc281847.aspx

Idea for finding the SQL Server instances using Powershell Script

See the below.. You can add the other two columns, as needed. I removed them due to nulls. But feel free to check for NULLs and replace with any value you see fit. I have tested it and it works.

$ServerList  = [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() | Select ServerName, IsClustered, Version, InstanceName

$ServerList | foreach { 
$i = $i - 1
    if ($_.Version -like "14.0.*" )         { $Versionresult ="SQL Server 2017"   }  
elseif ($_.Version -like "13.0.*" )         { $Versionresult ="SQL Server 2016"   } 
elseif ($_.Version -like "12.0.*" )         { $Versionresult ="SQL Server 2014"   }
elseif ($_.Version -like "11.0.*" )         { $Versionresult ="SQL Server 2012"   }  
elseif ($_.Version -like "10.50.*")         { $Versionresult ="SQL Server 2008 R2"}  
elseif ($_.Version -like "10.00.*")         { $Versionresult ="SQL Server 2008"   }
elseif ($_.Version -like "9.00.*" )         { $Versionresult ="SQL Server 2005"   } 
elseif ($_.Version -like "8.00.*" )         { $Versionresult ="SQL Server 2000"   } 

$table = [PSCustomObject] @{"ServerName" = $_.ServerName ; "IsClustured" = $_.IsClustered ;"Version" = $Versionresult ;"InstanceName" = $_.InstanceName}
$table

}

Blockquote

PowerShell - List all SQL instances on my system?

Just another way of doing it...can be a little quicker than SQLPS to get a quick answer.

(get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances

Finding SQL Server Instance & service pack build with PowerShell

Well, from the looks of it, it's a small mistake. When evaluating your $VersionBuild, you don't have any else statements. So if it fails the first evaluation:

if($versionBuild -eq $vs2008r2sp2)

Then it won't continue, and evaluate if it could be the other versions.

The other thing I would do is instead of using -eq in your if statements, use -match and shorten your version string. That way if your version string has a better chance of matching if the version string doesn't match exactly. e.g. You were trying to check the version $vs2005sp4 = "9.00.5000"; but the VersionString property is going to return "9.00.5000.00" which if you use -eq doesn't work, but if you use -match it will work.

So, adding in the else statements, shortening the version strings, and using -match your code block will look something like this:

# Sets the server versions
$vs2005sp4 = "9.00.5000";
$vs2008sp3 = "10.00.5500";
$vs2008r2sp2 = "10.50.4000";

#...

    # Set background color to green if service pack is 2008r2 SP2
    if($versionBuild -match $vs2008r2sp2)
    {
        $color = $greenColor           
}
else
{
        # Set background color to yellow if service pack is 2008 SP3
        if($versionBuild -match $vs2008sp3)
        {
            $color = $yellowColor
    }
    else
    {
            # Set background color to orange if service pack is 2005 SP4
            if($versionBuild -match $vs2005sp4)
            {
                $color = $orangeColor   

                # Create table data rows 
                #<td width='15%' align='center'>$instanceID</td>
                $dataRow = "
                    <tr>
                        <td width='10%'>$computer</td>                        
                        <td width='5%' align='center'>$versionBuild</td>
                        <td width='10%' align='center'>$versionName</td>
                    </tr>
                "
    }
    else
    {
                # If statement needed to remove label that were null
                If ($versionID -ne 'null') 
                {
                    Add-Content $servicePackReport $dataRow;
                    Write-Host -ForegroundColor DarkYellow "$computer $deviceID service pack build = $versionBuild";
                    $i++        
                }
            }
        }
    }

-- Edit

To answer @user1700796's question, I think that the real issue that @user1700796's encountering is that this script will not work against SQL servers that have multiple instances. i.e. when you get the Smo.Server object:

$svcPacks = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server($computer)

Where $computer is a server name, and on that server there is multiple instances; a single Smo.Server object is returned, which is essentially empty. i.e. pulling the VersionString returns an empty string.

If instead, you run the same command, except you put in the full SQL server name + the instance name, for each instance, then the Smo.Server object returns the correct information.

This is because the Smo.Server object uses a SQL connection string to connect to the SQL server (see the ConnectionContext property), and it can only connect to a single instance at a time, and does not enumerate through your instances as you might have been expecting.

So, instead of using the foreach loop on the Smo.Server object, you need to:

• First use a couple of commands to take the computer name and enumerate the names of the SQL server instances on that box
  
  
  • For ex. See: PowerShell list all SQL instances on my system
• Take those SQL server instances, and then do a foreach loop on those names
• You use those SQL Server instance names to get your Smo.Server object
• Then you can then pull the version string from the Smo.Server object.

How to query SQL Server using PowerShell

For SMO like you have in your question, you can run queries that return data using ExecuteWithResults() like so:

$s =  New-Object Microsoft.SqlServer.Management.Smo.Server "server instance"
$db = $s.Databases.Item("master")

$query = "SELECT * FROM [master].[sys].[databases] ORDER BY [name];"
$result = $db.ExecuteWithResults($query)

# Show output
$result.Tables[0]

---

Related Topics