--------因环境不同,请勿直接copy使用
1 --create procedure
create procedure usp_logshipping_send
as
declare
@hostname sysname
,@px int
,@sql varchar(500);
if OBJECT_ID(N'hostinfologshipping','U') is not null
drop table hostinfologshipping;
create table hostinfologshipping
(id int identity(1,1),
hostname sysname,
primary_database sysname,
backup_directory sysname
);
with hostname
as
(
select distinct HostName
from db_allhostinfo_database where IsLogShipping=1 and HostName<>'192.168.1.122'
)
select *,px=ROW_NUMBER()over(order by hostname)
into #hostname
from hostname;
select @hostname=quotename(hostname),@px=px from #hostname
where px=1;
while @@ROWCOUNT>0
begin
set @sql='insert into hostinfologshipping(hostname,primary_database,backup_directory) select '''+replace(replace(@hostname,'[',''),']','')+'''as hostname,* from openquery('+@hostname+',''select primary_database,backup_directory from msdb.dbo.log_shipping_primary_databases '') as b'
execute(@sql);
select top(1) @hostname=quotename(hostname),@px=px from #hostname where px>@px;
end
--2 -ssis package
3 every 10 minute get logshipping info for ssis package job
4 ssrs
