Query Maintenance Plan Backup Path

  • I am trying to figure out how to query the path from the "Back Up Database Task" from all subplans in SQL 2005. Is there a way to do this?

    I have discovered a few SP's that get me closer:

    Select * from dbo.sysmaintplan_plans

    Select * from dbo.sysmaintplan_subplans

    Select * from dbo.sysmaintplan_logdetail

    But nothing about the tasks specifically.

    Thanks,

    Nate

  • I believe I have it figured out:

    select physical_device_name from dbo.backupmediafamily

    Thanks,

    Nate

  • I didnt really understand the query :

    select physical_device_name from dbo.backupmediafamily

    could you explain? and can I try it too? 😉

    thanks

  • Did anyone figure out how to only return the default path and not all the backup files and their locations with it? I could REALLY use this query if anyone has it...

    Thanks in advance!

  • Here's the query if anyone needs it:

    if object_id('dbo.fn_SQLServerBUDir') is not null

    drop function dbo.fn_SQLServerBUDir

    go

    CREATE function dbo.fn_SQLServerBUDir()

    returns nvarchar(4000)

    as

    begin

    declare @rc int,

    @dir nvarchar(4000)

    exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'SQLArg1', @dir output, 'no_output'

    if (@dir is null)

    begin

    exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @dir output, 'no_output'

    select @dir = @dir + N'\Backup'

    end

    return @dir

    end

    go

    select fn_SQLServerBUDir = dbo.fn_SQLServerBUDir()

Viewing 5 posts - 1 through 4 (of 4 total)

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