Archive

Posts Tagged ‘enumerate registry values’

SQL Server – Identifying default data directory for multiple instances through registry

05.01.2012 9 comments

This is a follow up post from a comment on my blog.

Earlier I posted about using xp_instance_regread to get default data location for current instance. It translates a given registry path to instance specific registry path. This can return the value for a single instance only. However, if you need to locate default data directories for all available instances on a machine, you need to use xp_regread, which reads from an absolute registry path.

Registry path for default data location is same for different SQL Server versions:

HKLMSOFTWAREMicrosoftMicrosoft SQL Server<InstanceName>MSSQLServer

 

So the first thing we need before we can start reading data location from registry is list of instance name; these also can be read from registry using xp_instance_regenumvalues.

Registry path for instance name is as follows:

HKLMSOFTWAREMicrosoftMicrosoft SQL ServerInstance NamesSQL

We need to retrieve these to a temporary structure so that we can use it further:

CREATE TABLE #tempInstanceNames

(

      InstanceName      NVARCHAR(100),

      RegPath           NVARCHAR(100),

      DefaultDataPath   NVARCHAR(MAX)

)

 

INSERT INTO #tempInstanceNames (InstanceName, RegPath)

EXEC   master..xp_instance_regenumvalues

       @rootkey = N'HKEY_LOCAL_MACHINE',

       @key     = N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

      

SELECT      InstanceName, RegPath, DefaultDataPath

FROM        #tempInstanceNames

Result Set:

InstanceName  RegPath                    DefaultDataPath

MSSQLSERVER   MSSQL10_50.MSSQLSERVER     NULL

DENALI3       MSSQL11.DENALI3            NULL

SQL08ENT      MSSQL10.SQL08ENT           NULL

SQL05EXP      MSSQL.1                    NULL

We have a list of instance names; now we need to iteratively read registry value for each path; and append it to DefaultDataPath. The paths we need to read from registry will be:

1. HKLMSOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLServer

2. HKLMSOFTWAREMicrosoftMicrosoft SQL ServerMSSQL11.DENALI3MSSQLServer

3. HKLMSOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10.SQL08ENTMSSQLServer

4. HKLMSOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLServer

These needs to be read using xp_regread. I have used dynamic SQL to iterate through the list:

DECLARE     @SQL VARCHAR(MAX)

SET         @SQL = 'DECLARE @returnValue NVARCHAR(100)'

SELECT @SQL = @SQL + CHAR(13) +

'EXEC   master.dbo.xp_regread

@rootkey      = N''HKEY_LOCAL_MACHINE'',

@key          = N''SOFTWAREMicrosoftMicrosoft SQL Server' + RegPath + 'MSSQLServer'',

@value_name   = N''DefaultData'',

@value        = @returnValue OUTPUT;

 

UPDATE #tempInstanceNames SET DefaultDataPath = @returnValue

WHERE RegPath = ''' + RegPath + '''' + CHAR(13) FROM #tempInstanceNames

 

EXEC (@SQL)

 

SELECT      InstanceName, RegPath, DefaultDataPath

FROM        #tempInstanceNames

Result Set:

InstanceName         RegPath                    DefaultDataPath

MSSQLSERVER          MSSQL10_50.MSSQLSERVER     C:DatabaseData

DENALI3              MSSQL11.DENALI3            C:DatabaseDenaliData

SQL08ENT             MSSQL10.SQL08ENT           C:Database2008Data

SQL05EXP             MSSQL.1                    C:Database2005Data

I have updated the column in #tempInstanceNames so that it can be used further.

You can download the script from here.

Hope This Helps!

Vishal