T-sql to get the default backup path?

  • Hi all,

    Any way to get the default backup path using t-sql? I'd like to use it in a SP.

    cheers

  • EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory'

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • SQLRNNR (6/21/2012)


    EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory'

    Thanks.

    I'm aware of this but.. What I'm trying to achieve here is to avoid hard coded paths so that SP would be more generic. For instance say I have 5-10 instances on the same server, I need to go and check the path for each and specify the @key parameter? Isn't it possible to pull this from the system catalog?

  • It is only stored in the registry. You could record those default paths into a "control" table and use that instead. It may add extra admin overhead, but could be well worth it.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • clayman (6/21/2012)


    SQLRNNR (6/21/2012)


    EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory'

    Thanks.

    I'm aware of this but.. What I'm trying to achieve here is to avoid hard coded paths so that SP would be more generic. For instance say I have 5-10 instances on the same server, I need to go and check the path for each and specify the @key parameter? Isn't it possible to pull this from the system catalog?

    In case it was not clear in Jason's post, the @key parameter value remain consistent across all of your instances. The proc he is calling does proper registry resolution for us depending on the instance we run it from. That is the beauty of master.sys.xp_instance_regread, as opposed to master.sys.xp_regread which requires the literal path to the registry location you want. If you were using master.sys.xp_regread your calls would need to be different across all of your 5-10 instances.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Makes sense.. thanks very much for your replies.

  • You're welcome.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • You should be aware that the registry xp's are undocumented and subject to change at any time as are the paths of the values int eh registry. You can get it via a supported method via SMO. The defualt paths are properties of the Server Class.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Thank you all, This is what I was looking for too..

    Below is the codes I came up with...

    Declare @datadir nvarchar(4000)

    ,@logdir nvarchar(4000)

    ,@backupdir nvarchar(4000);

    EXEC master.dbo.xp_instance_regread

    N'HKEY_LOCAL_MACHINE'

    , N'Software\Microsoft\MSSQLServer\MSSQLServer'

    , N'DefaultData'

    , @datadir output;

    IF @datadir IS NULL

    BEGIN

    EXEC master.dbo.xp_instance_regread

    N'HKEY_LOCAL_MACHINE'

    , N'Software\Microsoft\MSSQLServer\Setup'

    , N'SQLDataRoot'

    , @datadir output;

    END

    EXEC master.dbo.xp_instance_regread

    N'HKEY_LOCAL_MACHINE'

    , N'Software\Microsoft\MSSQLServer\MSSQLServer'

    , N'DefaultLog'

    , @logdir output;

    EXEC master.dbo.xp_instance_regread

    N'HKEY_LOCAL_MACHINE'

    , N'Software\Microsoft\MSSQLServer\MSSQLServer'

    , N'BackupDirectory'

    , @backupdir output;

    SELECT @datadir as Data_directory, ISNULL(@logdir,@datadir) as Log_directory, @backupdir as Backup_directory;

  • Not very relevant any more, and I'm only posting it as a bit of trivia, but there is also a default path for full-text catalogs there.

    I wrote a script years ago to run against all of my sQL Servers to make sure the default paths were set according to the standards. I also include calls to xp_create_subdir to make sure the paths I set actually exists.

    Declare @DataDir nvarchar(4000),

    @LogDir nvarchar(4000),

    @BakDir nvarchar(4000),

    @FTDir nvarchar(4000),

    @DefDataDir nvarchar(4000),

    @DefLogDir nvarchar(4000),

    @DefBakDir nvarchar(4000),

    @DefFTDir nvarchar(4000),

    @Instance sysname

    Set @Instance = IsNull('\' + Cast(ServerProperty('InstanceName') as sysname), '')

    Set @DefDataDir = 'H:\mssql\data' + @Instance

    Set @DefLogDir = 'O:\mssql\data' + @Instance

    Set @DefBakDir = 'E:\mssql\bak' + @Instance

    Set @DefFTDir = 'H:\mssql\FTData' + @Instance

    Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',

    N'Software\Microsoft\MSSQLServer\MSSQLServer',

    N'DefaultData',

    @DataDir output,

    'no_output'

    Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',

    N'Software\Microsoft\MSSQLServer\MSSQLServer',

    N'DefaultLog',

    @LogDir output,

    'no_output'

    Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',

    N'Software\Microsoft\MSSQLServer\MSSQLServer',

    N'BackupDirectory',

    @BakDir output,

    'no_output'

    Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',

    N'Software\Microsoft\MSSQLServer\MSSQLServer',

    N'FullTextDefaultPath',

    @FTDir output,

    'no_output'

    Select Data = @DataDir, Logs = @LogDir, BAK = @BakDir, FTCatalogs = @FTDir

    If IsNull(@DataDir, '') <> @DefDataDir

    Begin

    Exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, @DefDataDir

    End

    If IsNull(@LogDir, '') <> @DefLogDir

    Begin

    Exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, @DefLogDir

    End

    If IsNull(@BakDir, '') <> @DefBakDir

    Begin

    Exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', REG_SZ, @DefBakDir

    End

    If IsNull(@FTDir, '') <> @DefFTDir

    Begin

    Exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'FullTextDefaultPath', REG_SZ, @DefFTDir

    End

    Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',

    N'Software\Microsoft\MSSQLServer\MSSQLServer',

    N'DefaultData',

    @DataDir output,

    'no_output'

    Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',

    N'Software\Microsoft\MSSQLServer\MSSQLServer',

    N'DefaultLog',

    @LogDir output,

    'no_output'

    Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',

    N'Software\Microsoft\MSSQLServer\MSSQLServer',

    N'BackupDirectory',

    @BakDir output,

    'no_output'

    Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',

    N'Software\Microsoft\MSSQLServer\MSSQLServer',

    N'FullTextDefaultPath',

    @FTDir output,

    'no_output'

    Select Data = @DataDir, Logs = @LogDir, BAK = @BakDir, FTCatalogs = @FTDir

    Exec xp_create_subdir @DataDir

    Exec xp_create_subdir @LogDir

    Exec xp_create_subdir @BakDir

    Exec xp_create_subdir @FTDir


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Robert Davis (6/22/2012)


    You should be aware that the registry xp's are undocumented and subject to change at any time as are the paths of the values int eh registry. You can get it via a supported method via SMO. The defualt paths are properties of the Server Class.

    Robert Davis (6/24/2012)


    Not very relevant any more, and I'm only posting it as a bit of trivia, but there is also a default path for full-text catalogs there.

    I wrote a script years ago to run against all of my sQL Servers to make sure the default paths were set according to the standards. I also include calls to xp_create_subdir to make sure the paths I set actually exists.

    Declare @DataDir nvarchar(4000),

    @LogDir nvarchar(4000),

    @BakDir nvarchar(4000),

    @FTDir nvarchar(4000),

    @DefDataDir nvarchar(4000),

    @DefLogDir nvarchar(4000),

    @DefBakDir nvarchar(4000),

    @DefFTDir nvarchar(4000),

    @Instance sysname

    Set @Instance = IsNull('\' + Cast(ServerProperty('InstanceName') as sysname), '')

    Set @DefDataDir = 'H:\mssql\data' + @Instance

    Set @DefLogDir = 'O:\mssql\data' + @Instance

    Set @DefBakDir = 'E:\mssql\bak' + @Instance

    Set @DefFTDir = 'H:\mssql\FTData' + @Instance

    Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',

    N'Software\Microsoft\MSSQLServer\MSSQLServer',

    N'DefaultData',

    @DataDir output,

    'no_output'

    Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',

    N'Software\Microsoft\MSSQLServer\MSSQLServer',

    N'DefaultLog',

    @LogDir output,

    'no_output'

    Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',

    N'Software\Microsoft\MSSQLServer\MSSQLServer',

    N'BackupDirectory',

    @BakDir output,

    'no_output'

    Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',

    N'Software\Microsoft\MSSQLServer\MSSQLServer',

    N'FullTextDefaultPath',

    @FTDir output,

    'no_output'

    Select Data = @DataDir, Logs = @LogDir, BAK = @BakDir, FTCatalogs = @FTDir

    If IsNull(@DataDir, '') <> @DefDataDir

    Begin

    Exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, @DefDataDir

    End

    If IsNull(@LogDir, '') <> @DefLogDir

    Begin

    Exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, @DefLogDir

    End

    If IsNull(@BakDir, '') <> @DefBakDir

    Begin

    Exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', REG_SZ, @DefBakDir

    End

    If IsNull(@FTDir, '') <> @DefFTDir

    Begin

    Exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'FullTextDefaultPath', REG_SZ, @DefFTDir

    End

    Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',

    N'Software\Microsoft\MSSQLServer\MSSQLServer',

    N'DefaultData',

    @DataDir output,

    'no_output'

    Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',

    N'Software\Microsoft\MSSQLServer\MSSQLServer',

    N'DefaultLog',

    @LogDir output,

    'no_output'

    Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',

    N'Software\Microsoft\MSSQLServer\MSSQLServer',

    N'BackupDirectory',

    @BakDir output,

    'no_output'

    Exec xp_instance_regread N'HKEY_LOCAL_MACHINE',

    N'Software\Microsoft\MSSQLServer\MSSQLServer',

    N'FullTextDefaultPath',

    @FTDir output,

    'no_output'

    Select Data = @DataDir, Logs = @LogDir, BAK = @BakDir, FTCatalogs = @FTDir

    Exec xp_create_subdir @DataDir

    Exec xp_create_subdir @LogDir

    Exec xp_create_subdir @BakDir

    Exec xp_create_subdir @FTDir

    Ok... I have to ask. Why didn't you use something like SMO for all of that?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • The script I posted pre-dates PowerShell. If I were to write it today, i would use PowerShell and SMO.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • For what it's worth this PowerShell:

    $v = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')

    $v.FullName

    $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') ".\SQL2012"

    $bkup = $s.Settings.BackupDirectory

    $bkup

    Yielded this output on my machine:

    Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

    C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup

    And yields this T-SQL when pointed at a SQL2012 instance (attained via quick Profiler Trace):

    declare @SmoAuditLevel int

    exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', @SmoAuditLevel OUTPUT

    declare @NumErrorLogs int

    exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', @NumErrorLogs OUTPUT

    declare @SmoLoginMode int

    exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', @SmoLoginMode OUTPUT

    declare @SmoDefaultFile nvarchar(512)

    exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @SmoDefaultFile OUTPUT

    declare @SmoDefaultLog nvarchar(512)

    exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @SmoDefaultLog OUTPUT

    declare @SmoMailProfile nvarchar(512)

    exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'MailAccountName', @SmoMailProfile OUTPUT

    declare @BackupDirectory nvarchar(512)

    exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @BackupDirectory OUTPUT

    declare @SmoPerfMonMode int

    exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'Performance', @SmoPerfMonMode OUTPUT

    if @SmoPerfMonMode is null

    begin

    set @SmoPerfMonMode = 1000

    end

    SELECT

    @SmoAuditLevel AS [AuditLevel],

    ISNULL(@NumErrorLogs, -1) AS [NumberOfLogFiles],

    (case when @SmoLoginMode < 3 then @SmoLoginMode else 9 end) AS [LoginMode],

    ISNULL(@SmoDefaultFile,N'') AS [DefaultFile],

    ISNULL(@SmoDefaultLog,N'') AS [DefaultLog],

    -1 AS [TapeLoadWaitTime],

    ISNULL(@SmoMailProfile,N'') AS [MailProfile],

    @BackupDirectory AS [BackupDirectory],

    @SmoPerfMonMode AS [PerfMonMode]

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply