SQL Server Features Discovery Report

2018-04-18

I don't need to validate SQL Server installations on a regular basis. When the need arises, my preference is to run the SQL Server features discovery report. Further, I prefer to run it from the command line. After looking up command line parameters one too many times, I decided to script it out.

It turns out the script commands are a little more complicated than I realized: there is a different setup.exe file for each version of SQL Server installed. I ended up making two script versions: a DOS batch file with hard-coded paths, and a PowerShell script that's more robust. Check them out and let me know what you think. (Keep scrolling down for a report sample image.)

*** Update 2018-04-23 ***
Scripts have been updated, based on information in the comments section.

Title SQL Server Discovery Report
echo off
COLOR 0A
cls

REM *****************************************************************************
REM * Runs the SQL Server Discovery Report (it should automatically open in 
REM * a browser window), which shows all the locally installed SQL Server 
REM * instances (if any), along with a listing of the installed features.
REM * 
REM * Author: Dave Mason
REM * https://twitter.com/BeginTry
REM * https://DaveMason.me/
REM *****************************************************************************

REM SQL Server 2019
IF EXIST "%PROGRAMFILES%\Microsoft SQL Server\150\Setup Bootstrap\SQL2019\setup.exe" (
"%PROGRAMFILES%\Microsoft SQL Server\150\Setup Bootstrap\SQL2019\setup.exe" /Action=RunDiscovery
GOTO End) 

REM SQL Server 2017
IF EXIST "%PROGRAMFILES%\Microsoft SQL Server\140\Setup Bootstrap\SQL2017\setup.exe" (
"%PROGRAMFILES%\Microsoft SQL Server\140\Setup Bootstrap\SQL2017\setup.exe" /Action=RunDiscovery
GOTO End) 


REM SQL Server 2016
IF EXIST "%PROGRAMFILES%\Microsoft SQL Server\130\Setup Bootstrap\SQLServer2016\setup.exe" (
"%PROGRAMFILES%\Microsoft SQL Server\130\Setup Bootstrap\SQLServer2016\setup.exe" /Action=RunDiscovery
GOTO End) 


REM SQL Server 2014
IF EXIST "%PROGRAMFILES%\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014\setup.exe" (
"%PROGRAMFILES%\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014\setup.exe" /Action=RunDiscovery
GOTO End) 


REM SQL Server 2012
IF EXIST "%PROGRAMFILES%\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012\setup.exe" (
"%PROGRAMFILES%\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012\setup.exe" /Action=RunDiscovery
GOTO End) 


REM *****************************************************************************
REM * Hat tip to Wayne Sheffield for providing the bootstrap setup.exe 
REM * filepaths for SQL 2008 R2, SQL 2008, and SQL 2005.
REM *  https://twitter.com/DBAWayne
REM *  https://blog.waynesheffield.com/wayne/
REM *****************************************************************************

REM SQL Server 2008 R2
IF EXIST "%PROGRAMFILES%\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\Setup.exe" (
"%PROGRAMFILES%\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\Setup.exe" /Action=RunDiscovery
GOTO End) 

REM SQL Server 2008
IF EXIST "%PROGRAMFILES%\Microsoft SQL Server\100\Setup Bootstrap\Release\Setup.exe" (
"%PROGRAMFILES%\Microsoft SQL Server\100\Setup Bootstrap\Release\Setup.exe" /Action=RunDiscovery
GOTO End) 

REM SQL Server 2005
IF EXIST "%PROGRAMFILES%\Microsoft SQL Server\90\Setup Bootstrap\Setup.exe" (
"%PROGRAMFILES%\Microsoft SQL Server\90\Setup Bootstrap\Setup.exe" /Action=RunDiscovery
GOTO End) 


REM TODO: Does the Discovery Report run the same way for SQL Server 6.5, 7, and 2000? 
REM (And does anyone care?)


ECHO No SQL Server instances found.
pause

:END

 <#
    .SYNOPSIS
        SQL Server discovery report.

    .DESCRIPTION
        Runs the SQL Server discovery report (opens in default browser).

    .INPUTS
        None

    .OUTPUTS
        None

    .NOTES
        Version:        1.1

        Author:         Dave Mason
            https://twitter.com/BeginTry
            https://DaveMason.me/

        Creation Date:  2018/04/18

        Assumptions:
            1. The sub-folder "Microsoft SQL Server" exists in %PROGRAMFILES%, 
                even if SQL was installed to a non-default path. This has been 
                verified on SQL 2008R2 and SQL 2012. Further verification may be needed.
            2. The numbered sub-folders in "%PROGRAMFILES%\Microsoft SQL Server" correlate to 
                installed versions of SQL Server. The numbers sync with database compatibility 
                levels. For example:
                        140     "%PROGRAMFILES%\Microsoft SQL Server\140"   SQL Server 2017
                        130     "%PROGRAMFILES%\Microsoft SQL Server\130"   SQL Server 2016
                        120     "%PROGRAMFILES%\Microsoft SQL Server\120"   SQL Server 2014
                        110     "%PROGRAMFILES%\Microsoft SQL Server\110"   SQL Server 2012
                        100     "%PROGRAMFILES%\Microsoft SQL Server\100"   SQL Server 2008 R2
                If this version/folder/naming convention remains intact for future versions, 
                this script should continue to work with no enhancements.
            3. The discovery report displays installed components for the version of SQL 
                Server associated with setup.exe, along with installed components of all 
                lesser versions of SQL Server that are installed.

        History:
     2018/04/23     DMason
            Output a message if no installed SQL Server features are found.
     Enhancements for older versions of SQL Server (2008, 2005).
            Thanks to Wayne Sheffield for verifying the Setup.exe Bootstrap path for 
                SQL Server 2008 R2 and for providing paths for SQL Server 2008 and 2005.
                https://twitter.com/DBAWayne
                https://blog.waynesheffield.com/wayne/

#>

#Locate the "%PROGRAMFILES%\Microsoft SQL Server" folder.
$MSSQLpath = [System.IO.Path]::Combine($env:ProgramFiles, "Microsoft SQL Server")
$lstCompatLevelDirs = New-Object "System.Collections.Generic.List[Int32]"

<#
    Iterate through the "Microsoft SQL Server" sub-folders.
    Sub-folder names that are numeric are added to List of type Int32.
#>
Get-ChildItem -Directory $MSSQLpath | 
    ForEach-Object {
        [Int32]$DirNum = 0

        if ([Int32]::TryParse($_.Name, [ref]$DirNum))
        {
            $lstCompatLevelDirs.Add($DirNum)
        }
    }

#Sort() the List, then Reverse() it so there is DESCENDING order.
$lstCompatLevelDirs.Sort()
$lstCompatLevelDirs.Reverse()

[bool] $setupExeFound = $false

<#
    Find the Setup Bootstrap Setup.exe file in the "highest" sub-folder.
    Here are a few examples:
        "%PROGRAMFILES%\Microsoft SQL Server\140\Setup Bootstrap\SQL2017\setup.exe"
        "%PROGRAMFILES%\Microsoft SQL Server\130\Setup Bootstrap\SQLServer2016\setup.exe"
        "%PROGRAMFILES%\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014\setup.exe"
        "%PROGRAMFILES%\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012\setup.exe"
        "%PROGRAMFILES%\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\Setup.exe"
#>
ForEach($int in $lstCompatLevelDirs)
{
    #The "Setup Bootstrap" path. For example: "%PROGRAMFILES%\Microsoft SQL Server\140\Setup Bootstrap
    [string]$SetupBootstrap = [System.IO.Path]::Combine(
        [System.IO.Path]::Combine($MSSQLpath, $int.ToString()),
        "Setup Bootstrap")

    if ([System.IO.Directory]::Exists($SetupBootstrap))
    {
        <#
            Iterate through the list of sub-folders with names that match the pattern "SQL*"
        #>
        ForEach($sqlSubDir in [System.IO.Directory]::GetDirectories($SetupBootstrap, "SQL*"))
        {
            <#
                Search for "setup.exe". 
                If found:
                    Run the exe with the appropriate parameters to run the discovery report.
                    Break out of the loops.
            #>
            [string]$setupExe = [System.IO.Path]::Combine($sqlSubDir, "setup.exe")

            if ([System.IO.File]::Exists($setupExe))
            {
                $setupExeFound = $true
                Start-Process -FilePath $setupExe -ArgumentList "/Action=RunDiscovery"
                break
            }
        }
    }

    if($setupExeFound)
    {
        break
    }
}

<#
    If the Setup.exe is still not found, search for it in hard-coded paths that correspond
    to older versions that didn't use the current version/folder/naming convention.

    2008: "%PROGRAMFILES%\Microsoft SQL Server\100\Setup Bootstrap\Release\Setup.exe"
    2005: "%PROGRAMFILES%\Microsoft SQL Server\90\Setup Bootstrap\Setup.exe"
#>
if(-Not $setupExeFound)
{
    $lstOldSqlVersionSetupExePaths = New-Object "System.Collections.Generic.List[string]"

    #SQL 2008
    $lstOldSqlVersionSetupExePaths.Add([System.IO.Path]::Combine($MSSQLpath, "100\Setup Bootstrap\Release\Setup.exe"))

    #SQL 2005
    $lstOldSqlVersionSetupExePaths.Add([System.IO.Path]::Combine($MSSQLpath, "90\Setup Bootstrap\Setup.exe"))

    #TODO: add strings to the array for even older versions of SQL (gulp).

    
    ForEach($setupExe in $lstOldSqlVersionSetupExePaths)
    {
        if ([System.IO.File]::Exists($setupExe))
        {
            $setupExeFound = $true
            Start-Process -FilePath $setupExe -ArgumentList "/Action=RunDiscovery"
            break
        }
    }
}

if(-Not $setupExeFound)
{
    Write-Host "No installed SQL Server features found." -ForegroundColor Yellow 
}

Comments