Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2002
    Hoffman Estates, IL

    Unanswered: 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
    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 @BackupDirectory

  2. #2
    Join Date
    Dec 2001
    Toronto, Canada
    Have you looked at xp_regread ?

    exec xp_regread 'HKEY_LOCAL_MACHINE',
    Value                 Data
    BackupDirectory  C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP

  3. #3
    Join Date
    Jun 2002
    Hoffman Estates, IL
    Thank you,

    xp_regread -- not in books on-line

    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.

    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'

    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=

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts