INVENTORY | LOG SHIPPING

INVENTORY SCRIPTS :  LOG SHIPPING
- Seel also parent script for databases with flag for log shipping
- The script can be run against SQL 2008 R2, 2008, 2005, and 2000.  The script is run as a dynamic query so SQL 2000 boxes will not error out on the tables it cannot find.


SELECT [Primary_Details].primary_id,
       [Monitor_Details].backup_job_id,
       [Primary_Details].primary_server,
       [Primary_Details].primary_database,
       [Secondary_Details].secondary_server,
       [Secondary_Details].secondary_database,
       [Monitor_Details].monitor_server,
       [Primary_Details].last_backup_date_utc,
       [LogShipping_JOBS].last_run_outcome       AS job_last_run_outcome,
       [LogShipping_JOBS].[last_outcome_message] AS [Job_last_outcome_message],
       [Monitor_Details].backup_directory        physical_path,
       [Monitor_Details].backup_share            shared_path,
       [Primary_Details].last_backup_file,
       [Primary_Details].backup_threshold,
       [Primary_Details].threshold_alert,
       [Primary_Details].threshold_alert_enabled,
       [Primary_Details].history_retention_period,
       [Monitor_Details].backup_retention_period,
       [Monitor_Details].user_specified_monitor,
       [LogShipping_JOBS].name                   AS job_name,
       [LogShipping_JOBS].[Enabled]              AS job_enabled,
       [LogShipping_JOBS].command                AS job_command,
       [LogShipping_JOBS].last_run_datetime      AS job_last_run_datetime,
       [LogShipping_JOBS].last_run_duration      AS job_last_run_duration,
       [LogShipping_JOBS].[description]
FROM   msdb.dbo.log_shipping_monitor_primary [Primary_Details]
       INNER JOIN msdb.dbo.log_shipping_primary_secondaries [Secondary_Details]
         ON ( [Primary_Details].primary_id = [Secondary_Details].primary_id )
       INNER JOIN msdb.dbo.log_shipping_primary_databases [Monitor_Details]
         ON ( [Primary_Details].primary_id = [Monitor_Details].primary_id
              AND [Secondary_Details].primary_id =
            [Monitor_Details].primary_id )
       LEFT OUTER JOIN (SELECT jobs.job_id,
                               jobs.name,
                               jobs.[enabled],
                               jobs.[description],
                               [JOBSteps].command,
                               CASE
                                 WHEN [JOBSteps].[last_run_date] > 0 THEN
                                 Substring(CAST([JOBSteps].[last_run_date] AS
                                                CHAR(8)),
                                 5, 2) + '/' +
                                 RIGHT(CAST([JOBSteps].[last_run_date]
                                            AS CHAR(8)), 2) + '/' + LEFT(CAST(
                                 [JOBSteps].[last_run_date] AS
                                 CHAR(8)), 4) + ' ' +
                                 LEFT(RIGHT('000000' + CAST(
                                            [JOBSteps].[last_run_time]
                                            AS VARCHAR(10)),
                                      6), 2)
                               + ':' + Substring(RIGHT('000000' + CAST(
                                       [JOBSteps].[last_run_time] AS
                                       VARCHAR(10)), 6),
                                               3, 2) + ':' + RIGHT(RIGHT(
                                                             '000000' + CAST(
                                 [JOBSteps].[last_run_time] AS
                                 VARCHAR(
                                 10)), 6), 2)
                                 ELSE NULL
                               END
              [Last_Run_DateTime],
                               LEFT(RIGHT('000000' + CAST(
                                          [SysJobServers].[last_run_duration] AS
                                          VARCHAR(10)),
                                    6), 2) + ':' + Substring(RIGHT(
                                                   '000000' + CAST(
                               [SysJobServers].[last_run_duration]
                               AS
                               VARCHAR(
                                                   10)), 6)
                               , 3, 2) + ':' + RIGHT(
                               RIGHT('000000' + CAST(
                                     [SysJobServers].[last_run_duration]
                                     AS VARCHAR(10)),
                                                   6)
                                     , 2) AS [Last_Run_Duration],
                               [SysJobServers].last_run_outcome,
                               [SysJobServers].[last_outcome_message]
                        FROM   msdb.dbo.sysjobs [Jobs]
                               INNER JOIN msdb.dbo.sysjobsteps [JOBSteps]
                                 ON ( jobs.job_id = [JOBSteps].job_id )
                               LEFT OUTER JOIN msdb.dbo.sysjobservers
                                               [SysJobServers]
                                 ON [Jobs].job_id = [SysJobServers].job_id
                        WHERE  [Jobs].category_id = 6) [LogShipping_JOBS]
         ON [Monitor_Details].backup_job_id = [LogShipping_JOBS].job_id