Checking MS SQL Server database files default paths

Introduction

A couple of days ago I faced an issue to get default paths for databases being created. It is simple to get it with system stored procedure xp_instance_regread. But the problem is to check these paths for all SQL Server versions starting from 8.0 (i.e. MS SQL Server 2000). It is easy to find it with your favorite search website for versions higher than 8.0, but this stuff seemed too old to use. No way, some people still use it!

Issues

For example, if one tries to use table variable to store xp_instance_regread result, he gets an error message.

DECLARE @paths TABLE (InstancePath VARCHAR(255))
DECLARE @value_name VARCHAR(500)
DECLARE @regInstanceKey VARCHAR(500)
SET @value_name = 'DefaultData'
INSERT INTO @paths
EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
                         @k = 'SOFTWARE\\Microsoft\\MSSQLServer\\MSSQLServer',
                         @vn = @value_name,
                         @s = @regInstanceKey OUTPUT;

The following error message appears while executing above query:

Msg 197, Level 15, State 1, Line 7
EXECUTE cannot be used as a source when inserting into a table variable.

So the solution could be a temporary table. This suits for all versions of MS SQL Server starting from 8.0

The second issue is SQL Server 2000 stores its instances not like its higher versions. So we need to know which version of SQL Server installed and which instance we are using at moment.

To get this one could check 'InstalledInstances' key values. This key stores all names of instances installed using space as separator:

DECLARE @regInstanceKey VARCHAR(500)
CREATE TABLE #instances (Name VARCHAR(255), InstanceName VARCHAR(255))
INSERT INTO #instances
EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
                         @key = 'SOFTWARE\\Microsoft\\Microsoft SQL Server',
                         @value_name = 'InstalledInstances',
                         @s = @regInstanceKey OUTPUT;
SELECT * FROM #instances
DROP TABLE #instances

We should get the result set of instances in one table after execution the query above:

After that we could ensure that we use the right instance

SELECT @ic = COUNT(*) FROM #instances
WHERE InstanceName = @@servicename

One might ask a question: why to use this instead of using @@servicename? We just ensure that our SQL Server instances were configured correctly. To check the current instance version is the only thing to be done.

DECLARE @ver VARCHAR(255)
DECLARE @version INT
SELECT @ver = SUBSTRING(CAST(SERVERPROPERTY('productversion') AS VARCHAR(255)), 1, 2)
IF(RIGHT(@ver,1) = '.') SET @version = CAST(LEFT(@ver,1) AS INT)
ELSE SET @version = CAST(@ver AS INT)
SELECT @version

Solution

So we almost there, now we're ready to write the full script for the subject

IF OBJECT_ID('tempdb..#instances') is not null DROP TABLE #instances
IF OBJECT_ID('tempdb..#directories') is not null DROP TABLE #directories

CREATE TABLE #instances (Name VARCHAR(255), InstanceName VARCHAR(255))
CREATE TABLE #directories (TypeName VARCHAR(255), DirPathName VARCHAR(255))

DECLARE @regInstanceKey VARCHAR(500), @s VARCHAR(500);
DECLARE @instance VARCHAR(255)       -- current instance
DECLARE @ver VARCHAR(255)            -- MS SQL version
DECLARE @key VARCHAR(255)            -- Registry key on server side
DECLARE @value_name VARCHAR(255)     -- Registry value name
DECLARE @instance_name VARCHAR(255)  -- Key value name for the instance of SQL Server 2000
DECLARE @version INT                 -- SQL Server version (integer)

DECLARE @ic INT
SET @instance_name = ''

INSERT INTO #instances
EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
                         @key     = 'SOFTWARE\\Microsoft\\Microsoft SQL Server',
                         @vn      = 'InstalledInstances',
                         @s       = @regInstanceKey OUTPUT;

SELECT @ic = COUNT(*) FROM #instances
 WHERE InstanceName = @@servicename
IF( @ic = 1 ) BEGIN
    SELECT @instance = InstanceName FROM #instances
    WHERE InstanceName = @@servicename
  
    SELECT @ver = SUBSTRING(CAST(SERVERPROPERTY('productversion') AS VARCHAR(255)), 1, 2)
    IF(RIGHT(@ver,1) = '.') SET @version = CAST(LEFT(@ver,1) AS INT)
    ELSE SET @version = CAST(@ver AS INT)

    IF  ( @version = 8) BEGIN
        IF (@instance <> 'MSSQLSERVER' ) SET @key = 
            'SOFTWARE\\Microsoft\\Microsoft SQL Server\\' + @instance + '\\MSSQLServer\\'
        ELSE                             SET @key = 'SOFTWARE\\Microsoft\\MSSQLServer\\MSSQLServer\\'
        END
    ELSE IF ( @version > 8) BEGIN
        EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
                                 @key = 'SOFTWARE\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL',
                                 @vn = @instance,
                                 @value = @instance_name out
        SET @key = 'SOFTWARE\\Microsoft\\Microsoft SQL Server\\' + @instance_name + '\\MSSQLServer'
    END

    SET @value_name = 'DefaultData'
    EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
                             @k = @key,
                             @vn = @value_name,
                             @s = @regInstanceKey OUTPUT;
    IF @regInstanceKey = @instance_name SET @regInstanceKey = NULL

    INSERT INTO #directories
    VALUES ('DefaultData', @regInstanceKey)
    SET @value_name = 'DefaultLog'

    EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
                             @k = @key,
                             @vn = @value_name,
                             @s = @regInstanceKey OUTPUT;

    IF @regInstanceKey = @instance_name SET @regInstanceKey = NULL
    INSERT INTO #directories
    VALUES ('DefaultLog', @regInstanceKey)

    SET @value_name = 'BackupDirectory'
    EXEC xp_instance_regread @rootkey = 'HKEY_LOCAL_MACHINE',
                             @k = @key,
                             @vn = @value_name,
                             @s = @regInstanceKey OUTPUT;

    INSERT INTO #directories
    VALUES ('BackupDirectory', @regInstanceKey)
    IF @regInstanceKey = @instance_name SET @regInstanceKey = NULL
END

SELECT * FROM #directories
DROP TABLE #instances
DROP TABLE #directories

After executing this we should expect something like the following result: