Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

default data/log file location Expand / Collapse
Author
Message
Posted Friday, June 27, 2008 12:51 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:20 PM
Points: 1,128, Visits: 925
I'm struggling to find a way to get the default data and log locations using t-sql. Is there a way to either do this without reading the registry? if not, does anyone have a script that will grab those values from the registry regardless of default/named instance?

I have one that works well for default instance, but I need one I can drop on any server and have it grab those values correctly.

Any help would be greatly appreciated.
Thanks
Post #525288
Posted Friday, June 27, 2008 1:39 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 9:45 AM
Points: 1,145, Visits: 1,922
Data:
-- ***************************************************************************
-- nvarchar(4000) = dbo.fn_SQLServerDataDir()
-- ***************************************************************************
IF OBJECT_ID('SQLServerDataDir_fn','FN') IS NOT NULL
DROP FUNCTION SQLServerDataDir_fn
GO

create function dbo.SQLServerDataDir_fn()
returns nvarchar(4000)
as
begin

declare @rc int,
@dir nvarchar(4000)
exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData', @dir output, 'no_output'

if (@dir is null)
begin
exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @dir output, 'no_output'
select @dir = @dir + N'\Data'
end

return @dir

end



Log
-- ***************************************************************************
-- nvarchar(4000) = dbo.SQLServerLogDir_fn()
-- ***************************************************************************
IF OBJECT_ID('SQLServerLogDir_fn','FN') IS NOT NULL
DROP FUNCTION SQLServerLogDir_fn
GO

create function dbo.SQLServerLogDir_fn()
returns nvarchar(4000)
as
begin

declare @rc int,
@dir nvarchar(4000)

exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultLog', @dir output, 'no_output'

if (@dir is null)
begin
exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @dir output, 'no_output'
select @dir = @dir + N'\Data'
end

return @dir

end



---
SQLSlayer
Making SQL do what we want it to do.

Post #525317
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse