|
Home > Archive > SQL server exams > June 2002 > Get Backup Directory
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Get Backup Directory
|
|
| Sturnfield 2002-06-20, 4:43 pm |
| 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_LPBS
TR 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(varcha
r(30),SERVERPROPERTY('serverna
me')))
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\Mi
crosoft' +
@regloc + '\MSSQLServer\BackupDirectory"'''
insert into #DtReg exec(@string)
select top 1 @BackupDirectory=substring(Bac
kupDirectory,8,1000) from #DtReg
Where BackupDirectory like 'REG_SZ%'
if @@rowcount<>1
Select * from #DtReg
drop table #DtReg
print @BackupDirectory | |
| Sturnfield 2002-06-21, 12:24 pm |
| /* get default backup location -- by Bob Sturnfield */
set nocount on
declare @regloc varchar(100),
@BackupDirectory varchar(1000)
select @regloc=
'Software\Microsoft\MSSQLServe
r\MSSQLServer'
if CHARINDEX('\', @@servername)>0
select @regloc='Software\Microsoft\Mi
crosoft 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 |
|
|
|
|