Query Maintenance Plan Backup Path

Question

Post reply

Query Maintenance Plan Backup Path

Nathan Rice

SSC Enthusiast

Points: 119
More actions
July 14, 2008 at 11:57 am

#188174

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

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

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

Nathan Rice SSC Enthusiast Points: 119 More actions · Answer 1

I believe I have it figured out:

select physical_device_name from dbo.backupmediafamily

Thanks,

Nate

RGP SSCarpal Tunnel Points: 4381 More actions · Answer 2

I didnt really understand the query :

select physical_device_name from dbo.backupmediafamily

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

thanks

mlemire Grasshopper Points: 14 More actions · Answer 3

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!

mlemire Grasshopper Points: 14 More actions · Answer 4

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()