How to determine the default database path in all SQL Server Versions

When you create a database in SQL Server using the "CREATE DATABASE" statement without any options, the database files will be created in some "default" locations. Many people think this default location is the same as the location of the master database, but this is incorrect, it is possible to alter the location in server properties after installation of the server. (in Management Studio right click on the server and select "Server Properties", then go to the "Database Settings" tab and near the bottom you will see a section with "Database Default locations") For example, suppose I execute the following statement:

CREATE DATABASE DefaultLocationDB

On my machine this means 2 files will be created:

• C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DefaultLocationDB.mdf
• C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DefaultLocationDB.ldf

This is nice, you don't have to remember a path to pass, but when you want to restore a database from another server (where a different path is used) it can be a pain. To restore a database with a specific location for the files we use the following syntax:

RESTORE DATABASE DefaultLocationDB 
FROM  DISK = N'c:\backups\DemoDB.bak' WITH  FILE = 1,  
MOVE N'demo_data_device' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoDb.ldf',  
MOVE N'demo_log_device' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DemoDb.ldf',  
NOUNLOAD, REPLACE

To make it easy to restore a database using a script I designed a function to figure out the location for the default Data path (and another one for the default log path). The locations of these paths I got from http://msdn.microsoft.com/en-us/library/ms143547.aspx The output can be stored in a 260 character variable (260 as specified MAX_PATH in http://msdn.microsoft.com/en-us/library/aa365247.aspx).

Edit: Rudi Larno posted a very nice refactored version of the code I posted. (especially note the nice recursion in it.)

View my old code

IF object_id('dbo.fn_get_default_data_path') is not null
    DROP FUNCTION dbo.fn_get_default_data_path
GO

CREATE FUNCTION dbo.fn_get_default_data_path()
returns nvarchar(260)
AS
BEGIN
    DECLARE @instance_name nvarchar(200), @system_instance_name nvarchar(200), @registry_key nvarchar(512), @data_path nvarchar(260), @log_path nvarchar(260);
 
    SET @instance_name = COALESCE(convert(nvarchar(20), serverproperty('InstanceName')), 'MSSQLSERVER')
    
    -- sql 2005/2008 with instance
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
    SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer';
 
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultData', @data_path output;

    IF @data_path is null -- sql 2005/2008 default instance
      BEGIN
        SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\Setup';
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'SQLDataRoot', @data_path output;
        SET @data_path = @data_path + '\Data';
      END
 
    if @data_path is null -- sql 2000 with instance
      begin
        SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer';
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultData', @data_path output;
      end
      
    if @data_path is null -- sql 2000 default instance
      begin
        SET @registry_key = N'Software\Microsoft\MSSQLServer\MSSQLServer';
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultData', @data_path output;
      end
      
      return @data_path
END
GO

As you can see it gets very ugly very fast. To extract the log path use this function:

IF object_id('dbo.fn_get_default_log_path') is not null
    DROP FUNCTION dbo.fn_get_default_log_path
GO

CREATE FUNCTION dbo.fn_get_default_log_path()
RETURNS nvarchar(260)
AS
BEGIN
    DECLARE @instance_name nvarchar(200), @system_instance_name nvarchar(200), @registry_key nvarchar(512), @log_path nvarchar(260);
 
    SET @instance_name = COALESCE(convert(nvarchar(20), serverproperty('InstanceName')), 'MSSQLSERVER');
    
    -- sql 2005/2008 instance
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
    SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer';
     
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultData', @data_path output;
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultLog', @log_path output;
 
    if @log_path is null -- sql 2000 with instance
      begin
        SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer';
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultLog', @log_path output;
      end
      
    if @log_path is null -- sql 2000 default instance
      begin
        SET @registry_key = N'Software\Microsoft\MSSQLServer\MSSQLServer';
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultLog', @log_path output;
      end    
    
    IF @log_path is null
      BEGIN
        SELECT @log_path = dbo.fn_get_default_data_path()
      END
      
      return @log_path
END
GO

IF object_id('dbo.fn_get_default_path') is not null
    DROP FUNCTION dbo.fn_get_default_path
GO

CREATE FUNCTION dbo.fn_get_default_path(@log bit)
RETURNS nvarchar(260)
AS
BEGIN
    DECLARE @instance_name nvarchar(200), @system_instance_name nvarchar(200), @registry_key nvarchar(512), @path nvarchar(260), @value_name nvarchar(20);

    SET @instance_name = COALESCE(convert(nvarchar(20), serverproperty('InstanceName')), 'MSSQLSERVER');

    -- sql 2005/2008 with instance
    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
    SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer';

    SET @value_name = N'DefaultData'
    IF @log = 1
      BEGIN
        SET @value_name = N'DefaultLog';
      END

    EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, @value_name, @path output;

    IF @log = 0 AND @path is null -- sql 2005/2008 default instance
      BEGIN
        SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\Setup';
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'SQLDataRoot', @path output;
        SET @path = @path + '\Data';
      END

    IF @path IS NULL -- sql 2000 with instance
      BEGIN
        SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer';
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, @value_name, @path output;
      END

    IF @path IS NULL -- sql 2000 default instance
      BEGIN
        SET @registry_key = N'Software\Microsoft\MSSQLServer\MSSQLServer';
        EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, @value_name, @path output;
      END

    IF @log = 1 AND @path is null -- fetch the default data path instead.
      BEGIN
        SELECT @path = dbo.fn_get_default_path(0)
      END

    RETURN @path;
END
GO

The functions can then be used as follows (or you can just copy paste the output):

declare @sql nvarchar(2000), 
        @data varchar(260), 
        @log varchar(260); 

select @data = dbo.fn_get_default_data_path(),
       @log = dbo.fn_get_default_log_path()

select @sql= 'RESTORE DATABASE DefaultLocationDB 
FROM  DISK = N''c:\backups\DemoDB.bak'' WITH  FILE = 1,  
MOVE N''demo_data_device'' TO N''' + @data + '\DemoDb.ldf'',  
MOVE N''demo_log_device'' TO N''' +  @log + '\DemoDb.ldf'',  
NOUNLOAD, REPLACE'

exec (@sql)

edit: I updated the script for dbo.fn_get_default_log_path() to avoid calling dbo.fn_get_default_data_path() when not needed.