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

Get a SQL Backup Folder Path in asp.net

Hi

Anyone knows , get a SQL Backup Folder Path in asp.net.Some Systems it displayed like D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup and some Systems like E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup and etc. i need to get a path for backup programmatically.Please help me.

Tamil

You can back up the databse to any location you'd like. You specify the path when you back up the DB.

sql