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

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

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

    Code:
    exec xp_regread 'HKEY_LOCAL_MACHINE',
     'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
     'BackupDirectory'
    
    Value                 Data
    -------------------------------------------------------------------------------
    BackupDirectory  C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP
    MCDBA

  3. #3
    Join Date
    Jun 2002
    Location
    Hoffman Estates, IL
    Posts
    14
    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.co...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

Posting Permissions

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