If you have a table that stores the SQL2005/2008 instances in your environment, and you have a utility server that has a linked server configured for each of these SQL2005/2008 instances, you can also use the following simple T-SQL to scan for and retrieve the log shipping configurations in your environment:
if object_id('tempdb..#log_shipping_entries') is not null
drop table #log_shipping_entries
create table #log_shipping_entries (
primary_server sysname,
secondary_server sysname,
database_name sysname
)
if object_id('tempdb..#log_shipping_monitor') is not null
drop table #log_shipping_monitor
create table #log_shipping_monitor (
status bit NULL,
is_primary bit NULL,
server sysname NULL,
database_name sysname NULL,
time_since_last_backup int NULL,
last_backup_file nvarchar(500) NULL,
backup_threshold int NULL,
is_backup_alert_enabled bit NULL,
time_since_last_copy int NULL,
last_copied_file nvarchar(500) NULL,
time_since_last_restore int NULL,
last_restored_file nvarchar(500) NULL,
last_restored_latency int NULL,
restore_threshold int NULL,
is_restore_alert_enabled bit NULL
)
declare @prod_server sysname,
@sql nvarchar(2000)
declare prod_cr cursor
for select distinct ServerName
<… You need to fill in the rest of the query to retrieve the
Server names in your environment>
OPEN prod_cr
FETCH NEXT FROM prod_cr into @prod_server
WHILE @@FETCH_STATUS = 0
BEGIN
select @sql = N'EXEC [' +
@prod_server +
N'].master..sp_help_log_shipping_monitor'
truncate table #log_shipping_monitor
insert #log_shipping_monitor
EXECUTE (@sql)
insert #log_shipping_entries
select distinct t1.server as primary_server,
t2.server as secondary_server, t2.database_name
from (select distinct server, database_name
from #log_shipping_monitor
where is_primary = 1) as t1,
#log_shipping_monitor t2
where t1.database_name = t2.database_name
and t2.is_primary = 0
FETCH NEXT FROM prod_cr into @prod_server
end
close prod_cr
deallocate prod_cr
select * from #log_shipping_entries