[Closed] PowerShell Script to pull multiple registry key values for SQL Server

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
 #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.

more ▼

asked Aug 16, 2011 at 06:57 AM in Default

avatar image

6.6k 21 26 34

(comments are locked)
10|1200 characters needed characters left

The question has been closed Aug 17, 2011 at 11:57 AM by Shawn_Melton for the following reason:

1 answer: sort voted first

Personally, I'd use LogParser to get this data from across my domain into a SQL table and work from there. This is for 2 reasons:
1 - I dont like PowerShell, it gives me a headache
2 - I love LogParser because its so easy

The basic query is

 logparser "select Path, KeyName, valuename, Value into <TableName> from
 '\\<SourceServername01>\\HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\'
 '\\<SourceServername02>\\HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\'
 '\\<SourceServername03>\\HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\' 
 where path like '%MSSQLServer%' and valuename like '%DefaultData%'" 
 -i:REG -o:SQL -database:<targetdb> -server:<DestServer\\instance> -createtable:on

You could get more specific but I dont have all the values you want to locate.

Then you can query this with TSQL.

more ▼

answered Aug 16, 2011 at 07:39 AM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

I got a headache a few times when I started learning PowerShell but it has smoothed out a little now. Customer I support has multiple domains and standalone SQL Servers, on different subnets that don't talk to each other. I also don't have a SQL Server instance that I could use for my own purposes, sure wish I did though. I'll be using a BMC product to push the script to the servers.

Aug 16, 2011 at 08:01 AM Shawn_Melton

Oh well, save LogParser for another time then. Sorry I cant help with PoSh. Someone smarter than me will be along soon...

Aug 16, 2011 at 08:10 AM Fatherjack ♦♦

Well I could just push LogParser with the script to run against it using the BMC product. It will let me push a file and then execute it as well...hmmmmm. Possibility.

Aug 16, 2011 at 08:17 AM Shawn_Melton

if the LogParser script has the right server/instance details then the results should come back at you. You can also automate LP to be executed by vbs or similar. check my blog for some details on LP uses http://www.simple-talk.com/community/blogs/jonathanallen/archive/category/1043.aspx

Aug 16, 2011 at 02:10 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 16, 2011 at 06:57 AM

Seen: 3932 times

Last Updated: Aug 16, 2011 at 08:02 AM

Copyright 2018 Redgate Software. Privacy Policy