SQL Server – Finding Database Default Locations

restoresql serversql-server-2008-r2

I am working on creating a script to restore database from a backup file to default location of an instance, which is set away from system databases installation folder.

Currently, I'm using to find default DATA Location

 SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
    FROM master.sys.master_files
    WHERE database_id = 1 AND file_id = 1

and default LOG Location 

 SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'mastlog.ldf', LOWER(physical_name)) - 1)
    FROM master.sys.master_files
    WHERE database_id = 1
        AND file_id = 2
    )

Is there any other way to find database default location with the help of query?

Thank you for time.

Best Answer

Your current method won't work, because it assumes that master was placed in the default data/log path, that it hasn't been moved at any point, and that the defaults haven't been changed since then.

The registry approaches work, but they are kind of tedious (especially if you don't use the instance-specific XPs and have to provide the registry path yourself), and who knows if this is where the locations will always be stored.

A different approach - assuming model doesn't have ridiculous file size settings - is to just create a database temporarily and see where SQL Server puts it by default.

USE master;
GO
CREATE DATABASE [!#floobernuggets];
GO
SELECT 
  [type] = CASE [type] WHEN 1 THEN 'data' ELSE 'log' END,
  [path] = SUBSTRING(physical_name, 1, 
    CHARINDEX(N'!#floobernuggets', physical_name)-1)
  FROM sys.master_files
  WHERE database_id = DB_ID(N'!#floobernuggets')
  ORDER BY [type];
GO
DROP DATABASE [!#floobernuggets];
GO

(This also assumes your default path doesn't actually contain the string !#floobernuggets already, and that you don't have any weird alerts or anything triggered off the actual creation of a database.)

Of course as Kin points out in SQL Server 2012 you will be able to use SERVERPROPERTY for this.

SELECT [data] = SERVERPROPERTY('InstanceDefaultDataPath'), 
       [log]  = SERVERPROPERTY('InstanceDefaultLogPath');
Related Question