Technical Article

Find Default Data and Log File Paths

,

A function to wrap Alex Aza's "find default paths" solution.

http://stackoverflow.com/a/12756990/377058

Uniquely fails back to [master] settings if defaults are not explicitly set.

Tested on SQL 2005.

/*
Get the default data and log paths of this instance.
If defaults are not explicitly defined, uses settings on [master].

Original source:
    Alex Aza,  http://stackoverflow.com/questions/1883071/how-do-i-find-the-data-directory-for-a-sql-server-instance

Example:
    select * from dbo.fn_DefaultDBFilePaths()

created 20130701 by wills
*/create function dbo.fn_DefaultDBFilePaths ( )
returns @DefaultPaths table (
     DefaultData varchar(200) ,
     DefaultLog varchar(200)
    )
as 
begin

    declare @DefaultData nvarchar(512)
    exec master.dbo.xp_instance_regread 
        N'HKEY_LOCAL_MACHINE' ,
        N'Software\Microsoft\MSSQLServer\MSSQLServer' ,
        N'DefaultData' ,
        @DefaultData output

    declare @DefaultLog nvarchar(512)
    exec master.dbo.xp_instance_regread 
        N'HKEY_LOCAL_MACHINE' ,
        N'Software\Microsoft\MSSQLServer\MSSQLServer' ,
        N'DefaultLog' ,
        @DefaultLog output

    declare @MasterData nvarchar(512)
    exec master.dbo.xp_instance_regread 
        N'HKEY_LOCAL_MACHINE' ,
        N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters' ,
        N'SqlArg0' ,
        @MasterData output
    select  @MasterData = substring(@MasterData, 3, 255)
    select  @MasterData = substring(@MasterData, 1, len(@MasterData) - charindex('\', reverse(@MasterData)))

    declare @MasterLog nvarchar(512)
    exec master.dbo.xp_instance_regread 
        N'HKEY_LOCAL_MACHINE' ,
        N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters' ,
        N'SqlArg2' ,
        @MasterLog output
    select  @MasterLog = substring(@MasterLog, 3, 255)
    select  @MasterLog = substring(@MasterLog, 1, len(@MasterLog) - charindex('\', reverse(@MasterLog)))

    insert  into @DefaultPaths
            select  isnull(@DefaultData, @MasterData) DefaultData ,
                    isnull(@DefaultLog, @MasterLog) DefaultLog        

    return 
end
GO

Rate

2 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

2 (1)

You rated this post out of 5. Change rating