SET NOEXEC OFF
DECLARE @primaryORsecondaryserver varchar(100);
DECLARE @PrimaryServerBackupType varchar(100);
DECLARE @SecondaryServerCopyType varchar(100);
SET @primaryORsecondaryserver = (SELECT top 1 agent_type from [msdb].[dbo].[log_shipping_monitor_history_detail])
SET @PrimaryServerBackupType = '0'
SET @SecondaryServerCopyType = '1'
IF (@primaryORsecondaryserver = @PrimaryServerBackupType )
BEGIN
SELECT
MP.[primary_server] as [Primary Server]
,MP.[primary_database]
,PB.[backup_directory]
,PB.[backup_share]
,MP.[last_backup_file]
,PB.[backup_retention_period]
,MP.[backup_threshold]
,MP.[last_backup_date]
FROM [msdb].[dbo].[log_shipping_monitor_primary] AS MP
JOIN [msdb].[dbo].[log_shipping_primary_databases] AS PB
ON mp.primary_id= PB.primary_id
ORDER BY primary_database
SET NOEXEC ON
END
IF (@primaryORsecondaryserver = @SecondaryServerCopyType )
BEGIN
SELECT
[monitor_server] as [Secondary Server]
,[primary_server]
,[primary_database]
,[backup_source_directory]
,[backup_destination_directory]
,[last_copied_file]
,[last_copied_date]
,[file_retention_period]
FROM [msdb].[dbo].[log_shipping_secondary]
ORDER BY primary_database
SET NOEXEC ON
END
GO
