I'm working on a script that is focused on pulling registry key values for directories used by SQL Server installations. This is the easiest way I can see to get them without having to use SMO or SQLPS, since I'll be working with different flavors of SQL Server. Where I am stuck is more logic in how to best do it than anything else. Everything I need is under the registry hive *HKLM:\\SOFTWARE\\Microsoft\\Microsoft SQL Server*. When you install an instance of SQL Server a sub key is created for your instance. The name of that subkey depends on the installation you did, default or named, and how many instances the server has installed. I have gotten down this code in order to pull out what instances are on a server, so I can build the full registry key path to get to the values I need: $regInstance = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances if ($regInstance.Count > 1) { "More than one instance found on server" foreach ($ins in $RegInstance) { $regPath = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$ins #build full path to use in script later on #the value in this key is the MSSQL.1 or MSSQL.2 with SQL Server 2005 or # the MSSQL10.MSSQLSERVER or MSSQL10.MyNamedInstance with SQL 2008 or # the MSSQL10_50.MSSQLServer or MSSQL50_10.MyNamedInstance with SQL 2008 R2 $fullpath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$regpath" #have another portion does the same thing to get path for SSRS if Test-Path founds ...\Instance Names\RS exist } } else { #repeat the above just one time } With multiple ways to do some things in PowerShell, is there a better way to handle the above code? My first though I had typing this question up was to put the instance names in a hashtable.** Where I am stuck in the logic is how to handle the $fullpath key once I have it built and then iterate through the registry keys I need to check. Then when I check them how to store the path (if it has one) to pass into a function I have written that will do something with the path.** The registry keys...I have about 15 or so keys that may or may not exist under the $fullpath to get. An example would be the DefaultData and DefaultLog keys. They only exist if someone went into the SQL Server instance properties and put the path in. If you enter the Get-ItemProperty for a key that does not exist or does not have a value, it just returns you back to a prompt with no output. So instead of trying to do Test-Path on each registry key I figured I could somehow in the logic just check for "-ne $null". The registry keys:
(Get-ItemProperty "$fullpath\\MSSQLServer").DefaultData
(Get-ItemProperty "$fullpath\\MSSQLServer").DefaultLog
(Get-ItemProperty "$fullpath\\MSSQLServer").BackupDirectory
(Get-ItemProperty "$fullpath\\MSSQLServer").FullTextDefaultPath
(Get-ItemProperty "$fullpath\\Replication").WorkingDirectory
(Get-ItemProperty "$fullpath\\Setup").SQLBinRoot
(Get-ItemProperty "$fullpath\\Setup").SQLDataRoot
(Get-ItemProperty "$fullpath\\Setup").SQLPath
(Get-ItemProperty "$fullpath\\Setup").SQLProgramDir
(Get-ItemProperty "$fullpath\\SQLServerAgent").ErrorLogFile
(Get-ItemProperty "$fullpath\\SQLServerAgent").WorkingDirectory
(Get-ItemProperty "$fullpath\\Setup").SQLPath
(Get-ItemProperty "$fullpath\\Setup").DataDir
(Get-ItemProperty "$fullpath\\Setup").SQLBinRoot
(Get-ItemProperty "$fullpath\\Setup").SQLPath
(Get-ItemProperty "$fullpath\\Setup").SQLProgramDir
If additional info is needed or explanation please let me know.
