Parse MS SQL BAK file information returned by sqlcmd

mssql-utils.ps1

Function get-MSSQLBAKVersion {
param(
[parameter(Mandatory=$true)]
[ValidateScript({Test-Path -Path $_  -pathType leaf})]
[String]$bakfile
)
    $query = "restore headeronly from disk=N'{0}' with nounload" -f $bakfile
    $bakInfo = & SQLCMD.EXE  -s"," -Q $query | Select-String -NotMatch '^-|\('|convertfrom-csv
    $sqlVersions = @{ 
        "869" = "SQL Server 2017"
        "852" = "SQL Server 2016"
        "782" = "SQL Server 2014"
        "706" = "SQL Server 2012"
        "684" = "SQL Server 2012 CTP1"
        "660" = "SQL Server 2008 R2"
        "661" = "SQL Server 2008 R2"
        "655" = "SQL Server 2008"
        "612" = "SQL Server 2005 SP2+ with VarDecimal enabled"
        "611" = "SQL Server 2005"
        "539" = "SQL Server 2000"
        "515" = "SQL Server 7.0"
        "408" = "SQL Server 6.5"
    }
    if ($sqlVersions.ContainsKey($bakInfo.DatabaseVersion)) {
        $sqlVersion = $sqlVersions[$bakInfo.DatabaseVersion]
    }
    else {
        $sqlVersion = "UNKNOWN database"
    }
    Add-Member -InputObject $bakInfo -NotePropertyName "SQLServer" -NotePropertyValue $sqlVersion

    $compatLevels = @{
        "140" = "SQL Server 2017"
        "130" = "SQL Server 2016"
        "120" = "SQL Server 2014"
        "110" = "SQL Server 2012, SQL Server 2012 CTP1"
        "100" = "SQL Server 2008 R2, SQL Server 2008"
        "90" = "SQL Server 2005 SP2+, SQL Server 2005"
        "80" = "SQL Server 2000"
        "70" = "SQL Server 7.0"
        "65" = "SQL Server 6.5"
    }
    if ($compatLevels.ContainsKey($bakInfo.CompatibilityLevel)) {
        $compatLevel = $compatLevels[$bakInfo.CompatibilityLevel]
    }
    else {
        $compatLevel = "UNKNOWN compatibility"
    }
    Add-Member -InputObject $bakInfo -NotePropertyName "SQLCompatibility" -NotePropertyValue $compatLevel

    $bakInfo
}

get-MSSQLBAKVersion  'C:\temp\AdventureWorks2012.bak'