x

[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
   }
}
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.
more ▼

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

Shawn_Melton gravatar image

Shawn_Melton
5.3k 20 21 29

(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 oldest

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

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x28
x3

asked: Aug 16, 2011 at 06:57 AM

Seen: 2579 times

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