Getting SQL Server 2005 Default Data and Log Locations... My Way

My current project uses backup devices to accomplish a few tasks. The scripts that I wrote to create these backup devices used hard-coded paths until the project required the backup devices to use the drive that the SQL Server 2005 instance points to for its default database file locations. Therefore, I needed a way to find that drive letter.

Immediate results for using T-SQL to get those default locations suggested using an undocumented system stored procedure, xp_instance_regread. This sproc works just fine, returning instance-specific registry values. Pretty slick, if you ask me.

However, I don't like using something that I don't understand. In this case, I didn't put together how you could expect a "DefaultLog" or "DefaultData" key to exist using a registry path like "Software\MSSQLServer\MSSQLServer" with SQL Server 2005. If you browse the registry, you won't find a "DefaultData" or "DefaultLog" key at that location. I sure didn't. In fact, I found very little at that location, which puzzled me, and I didn't find any explanation for this behavior anywhere on the Internet.

On the other hand, I understood how xp_regread works. It will read only keys that you can find by hand. So, I came up with these UDF's that do the same job as xp_instance_regread in a way that I can feel comfortable about:

create function udf_GetDefaultDataFilePath ()
returns nvarchar(260)
as
begin
    declare @SystemInstanceName nvarchar(200),
            @RegKey nvarchar(512),
            @Path nvarchar(260);
 
    set @SystemInstanceName = dbo.udf_GetSystemInstanceName();
 
    set @RegKey = N'Software\Microsoft\Microsoft SQL Server\' +
                    @SystemInstanceName + '\MSSQLServer';
 
    exec master.dbo.xp_regread
            N'HKEY_LOCAL_MACHINE',
            @RegKey,
            N'DefaultData',
            @Path output;
 
    if @Path is null
    begin
        set @RegKey = N'Software\Microsoft\Microsoft SQL Server\' +
                        @SystemInstanceName + '\Setup';
  
        exec master.dbo.xp_regread
            N'HKEY_LOCAL_MACHINE',
            @RegKey,
            N'SQLDataRoot',
            @Path output;
  
        set @Path = @Path + '\Data';
    end
 
    return @Path;
end

create function udf_GetDefaultLogFilePath ()
returns nvarchar(260)
as
begin
    declare @SystemInstanceName nvarchar(200),
            @RegKey nvarchar(512),
            @Path nvarchar(260);
 
    set @SystemInstanceName = dbo.udf_GetSystemInstanceName();
 
    set @RegKey = N'Software\Microsoft\Microsoft SQL Server\' +
                    @SystemInstanceName + '\MSSQLServer';
 
    exec master.dbo.xp_regread
            N'HKEY_LOCAL_MACHINE',
            @RegKey,
            N'DefaultLog',
            @Path output;
 
    if @Path is null
    begin
        set @RegKey = N'Software\Microsoft\Microsoft SQL Server\' +
                        @SystemInstanceName + '\Setup';
  
        exec master.dbo.xp_regread
            N'HKEY_LOCAL_MACHINE',
            @RegKey,
            N'SQLDataRoot',
            @Path output;
  
        set @Path = @Path + '\Data';
    end
 
    return @Path;
end

create function udf_GetSystemInstanceName ()
returns nvarchar(10)
as
begin
    declare @InstanceName nvarchar(200),
            @SystemInstanceName nvarchar(10);

    set @InstanceName = convert(nvarchar(20), serverproperty('InstanceName'));

    if @InstanceName is null
        set @InstanceName = 'MSSQLSERVER';

    exec master.dbo.xp_regread
            N'HKEY_LOCAL_MACHINE',
            N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL',
            @InstanceName,
            @SystemInstanceName output;

    return @SystemInstanceName
end

The registry keys reside in an instance-specific location, so udf_GetSystemInstanceName() figures out the underlying name for the instance. (The first instance is "MSSQL.1", the second is "MSSQL.2", and so on.) The other two use this value to compose the registry path where the "DefaultData" and "DefaultLog" keys reside, respectively. If the key does not exist, each function returns the default default location, the typical "<installdir>\<instance>\MSSQL\Data".