Get Backup Directory

I am writing a stored procedure, and I want to set a variable to the default Backup Directory for the current instance. (7.0 or SQL2000).

What is the Standard"way of doing this

Do I have to write a C program that calls HRESULT GetBackupDirectory(SQLDMO_LPBSTR pRetVal);

Or should I install DtReg.exe on all my servers?

/* get default backup location -- by Bob Sturnfield */
--DtReg.exe can be found at http://www.tamedos.com/downloads
set nocount on
declare @.string varchar(4000),
@.regloc varchar(100),
@.BackupDirectory varchar(1000),
@.servernm varchar(30)

select @.regloc='MSSQLServer'
select @.servernm=rtrim(convert(varchar(30),SERVERPROPERTY ('servername')))
if CHARINDEX('\', @.servernm)>0
select @.regloc='Microsoft SQL Server\' + substring(@.servernm, CHARINDEX('\', @.servernm)+1, 30)

create table #DtReg( BackupDirectory varchar(4000))

select @.string='xp_cmdshell ''DtReg -ListValue "HKEY_LOCAL_MACHINE\Software\Microsoft\' +
@.regloc + '\MSSQLServer\BackupDirectory"'''

insert into #DtReg exec(@.string)

select top 1 @.BackupDirectory=substring(BackupDirectory,8,1000) from #DtReg
Where BackupDirectory like 'REG_SZ%'

if @.@.rowcount<>1
Select * from #DtReg

drop table #DtReg

print @.BackupDirectoryHave you looked at xp_regread ?

exec xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
'BackupDirectory'

Value Data
------------------------
BackupDirectory C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP|||Thank you,

xp_regread -- not in books on-line

http://www.4guysfromrolla.com/webtech/101499-1.shtml

There's a little known xp procedure in the master database called xp_regread. This stored procedure accepts three parameters. The first one being the root key, next is the path to the key, and finally the key value you are looking to return.

http://www.swynk.com/friends/green/xp_reg.asp

xp_regread [@.rootkey=]'rootkey', [@.key=]'key'[, [@.value_name=]'value_name'][, [@.value=]@.value OUTPUT]

xp_regwrite [@.rootkey=]'rootkey', [@.key=]'key', [@.value_name=]'value_name', [@.type=]'type', [@.value=]'value'

http://www.sql-server-performance.com/ac_extended_stored_procedures.asp

These extended stored procedures work with SQL Server 7.0, as well as with SQL Server 2000.

This is great, I very much appreciate the response

Bob Sturnfield

/* get default backup location -- by Bob Sturnfield */
set nocount on
declare @.regloc varchar(100),
@.BackupDirectory varchar(1000)

select @.regloc=
'Software\Microsoft\MSSQLServer\MSSQLServer'

if CHARINDEX('\\', @.@.servername)>0
select @.regloc='Software\Microsoft\Microsoft SQL Server\' +
substring(@.@.servername, CHARINDEX('\', @.@.servername)+1, 30)+ '\MSSQLServer'

execute master..xp_regread @.rootkey='HKEY_LOCAL_MACHINE',
@.key=@.regloc, @.value_name='BackupDirectory',
@.value=@.BackupDirectory OUTPUT

print @.BackupDirectory