?
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
function getSqlInfo{
    $results=@()
    $instances=(get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
    foreach ($i in $instances){
        $p=(Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$i
        $instance=Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$p\Setup"
        $name=(Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$i
        $edition=$instance.Edition
        $version=$instance.Version
        $label = switch ($version) {
                "14.0.3076.1" {"SQL Server 2017 CU14"; break}
                "14.0.3006.16" {"SQL Server 2017 CU1"; break}
                "14.0.2000.63" {"SQL Server 2017 RTM with Security Update KB4057122"; break}
                "14.0.1000.169" {"SQL Server 2017 (vNext) RTM"; break}
                "13.0.1601.5" {"SQL Server 2016 RTM"; break}
                "12.0.5207.0" {"SQL Server 2014 SP2 with Security Update KB4019093"; break}
                "12.2.5000.0" {"SQL Server 2014 SP2"; break}
                default {"Unknown Version"; break}
            }
        $bootStrap=switch ($version.Substring(0,2)) {
        "14" {"C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\SQL2017"; break}
        "13" {"C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\SQLServer2016"; break}
        "12" {"C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014"; break}
        "11" {"C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012"; break}
        default {"Unknown"; break}
        }
    $bin=switch ($version.Substring(0,2)) {
        "14" {"C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn"; break}
        "13" {"C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn"; break}
        "12" {"C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn"; break}
        "11" {"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn"; break}
        default {"Unknown"; break}
        }
        $backupDirectory=Get-ItemPropertyValue -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$name\MSSQLServer" -Name 'BackupDirectory'
        $dataDirectory=Get-ItemPropertyValue -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$name\Setup" -Name 'SQLDataRoot'
        $masterDataDirectory=Get-ItemPropertyValue -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$name\MSSQLServer\Parameters" -Name 'SQLArg0' | ForEach-Object {$_.Substring(2)}
        $masterLogDirectory=Get-ItemPropertyValue -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$name\MSSQLServer\Parameters" -Name 'SQLArg2' | ForEach-Object {$_.Substring(2)}
    $result=[pscustomobject]@{
        version=$version
        name=$name
        edition=$edition
        label=$label
        backupDirectory=$backupDirectory
        dataDirectory=$dataDirectory
        masterDataDirectory=$masterDataDirectory
        masterLogDirectory=$masterLogDirectory
        bin=$bin
        bootStrap=$bootStrap
        }
    $results+=$result
    }
    return $results
}
?
01
02
03
04
05
06
07
08
09
10
11
12
PS C:\Users\sqlAdmin> getSqlInfo
 
bin                 : C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn
bootStrap           : C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\SQL2017
version             : 14.0.1000.169
name                : MSSQL14.MSSQLSERVER
edition             : Standard Edition
label               : SQL Server 2017 (vNext) RTM
backupDirectory     : C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup
dataDirectory       : C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL
masterDataDirectory : C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\master.mdf
masterLogDirectory  : C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
Categories: