Technical Article

Get Data, Log, Backup, and Binn Directory (SQL Server 2005)

,

Run in SSMS or Query Analyzer

Set nocount on

declare @tblpaths table (
[property]varchar (100),
[path]varchar (200)
)

declare 
@returncodeint,
@instancenamevarchar (100),
@pathnvarchar(4000)

/*
get instance name
*/Select @instancename = @@servername
insert into @tblpaths ([property], [path])
select 'Instance Name', @instancename


/*
find default data path
*/


exec @returncode = master.dbo.xp_instance_regread N'hkey_local_machine',N'software\microsoft\mssqlserver\mssqlserver',N'defaultdata', @path output, 'no_output'

if @path is null
begin

exec @returncode = master.dbo.xp_instance_regread N'hkey_local_machine',N'software\microsoft\mssqlserver\setup',N'sqldataroot', @path output, 'no_output'
set @path = @path + '\data'
insert into @tblpaths ([property], [path])
select 'Database Data Directory', @path
end
else
begin
insert into @tblpaths ([property], [path])
select 'Database Data Directory',  @path
end

/*
find default log path
*/
exec @returncode = master.dbo.xp_instance_regread N'hkey_local_machine',N'software\microsoft\mssqlserver\mssqlserver',N'defaultlog', @path output, 'no_output'

if @path is null
begin

exec @returncode = master.dbo.xp_instance_regread N'hkey_local_machine',N'software\microsoft\mssqlserver\setup',N'sqldataroot', @path output, 'no_output'
set @path = @path + '\data'
insert into @tblpaths ([property], [path])
select 'Database Log Directory', @path
end
else
begin
insert into @tblpaths ([property], [path])
select 'Database Log Directory', @path
end


/*
find binn directory
*/exec @returncode = master.dbo.xp_instance_regread N'hkey_local_machine',N'software\microsoft\mssqlserver\setup',N'sqlbinroot', @path output, 'no_output'

insert into @tblpaths ([property], [path])
select 'Binn Directory', @path


/*
find backup directory
*/exec @returncode = master.dbo.xp_instance_regread N'hkey_local_machine',N'software\microsoft\mssqlserver\mssqlserver',N'backupdirectory', @path output, 'no_output'
insert into @tblpaths ([property], [path])
select 'Backup Directory', @path

select * from @tblpaths

Rate

5 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (5)

You rated this post out of 5. Change rating