July 14, 2008 at 11:57 am
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
July 14, 2008 at 12:23 pm
I believe I have it figured out:
select physical_device_name from dbo.backupmediafamily
Thanks,
Nate
July 15, 2008 at 8:36 am
I didnt really understand the query :
select physical_device_name from dbo.backupmediafamily
could you explain? and can I try it too? 😉
thanks
January 13, 2010 at 5:06 pm
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!
January 14, 2010 at 6:41 am
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