sp_upgrade_log_shipping (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_upgrade_log_shipping()

MetaData:

 create procedure sys.sp_upgrade_log_shipping   
as  
begin  
    set nocount on  
    declare @retcode int  
                ,@object_id int  
                ,@toolpath nvarchar(260)  
  
    --   
    --  skip upgrading Yukon Log shipping if edition is not   
    --  Web, Standard, Developer, Enterprise, DataCenter or Workgroup  
    --   
    if (cast(SERVERPROPERTY('EngineEdition') as int) not in (2,3))  
        return 0  
    --   
    --  First invoke the logshipping install SP  
    --  This will do MSDB existence check  
    --  install any new metadata needed  
    --   
    exec sys.sp_logshippinginstallmetadata  
    --   
    --  Now do a explicit security check  
    --  to catch any edition or security issue  
    --   
    exec @retcode = sys.sp_MSlogshippingsysadmincheck  
    if (@retcode != 0 or @@error != 0)  
        return 1  
    --   
    --  must be invoked from master db  
    --   
    if (db_name() != N'master')  
    begin  
        raiserror(5001, 16,-1)  
        return 1  
    end  
    --   
    --   
    --  Upgrade only yukon installs (needed if this code goes out of resource db)  
    --   
    if (@@microsoftversion / 0x01000000) < 9  
    begin  
        raiserror(N'This procedure should be executed against 9.0 servers.', 16, -1)   
        return 1  
    end  
     --   
    --   
    --  Starting upgrade  
    --   
    select @object_id = object_id(N'msdb.dbo.log_shipping_primary_databases')  
    if @object_id is not null  
    begin  
        --  columns  
        if not exists (select * from msdb.sys.columns where object_id = @object_id  
                            and name = N'user_specified_monitor')  
        begin  
            alter table msdb.dbo.log_shipping_primary_databases  
                add user_specified_monitor bit null  
        end  
  
        if not exists (select * from msdb.sys.columns where object_id = @object_id  
                            and name = N'backup_compression')  
        begin  
            alter table msdb.dbo.log_shipping_primary_databases  
                add backup_compression tinyint not null default 2  -- (default to server default)  
        end  
        --  indices  
        if not exists (select * from msdb.sys.indexes where object_id = @object_id  
                            and name = N'uc1lsprimary_databases')  
        begin  
            create unique nonclustered index uc1lsprimary_databases   
                on msdb.dbo.log_shipping_primary_databases (primary_database)  
        end  
        if not exists (select * from msdb.sys.indexes where object_id = @object_id  
                            and name = N'nc1lsprimary_databases')  
        begin  
            create nonclustered index nc1lsprimary_databases   
                on msdb.dbo.log_shipping_primary_databases (monitor_server)  
        end  
        if not exists (select * from msdb.sys.indexes where object_id = @object_id  
                            and name = N'nc2lsprimary_databases')  
        begin  
            create nonclustered index nc2lsprimary_databases   
                on msdb.dbo.log_shipping_primary_databases (user_specified_monitor)  
        end  
    end  
  
    select @object_id = object_id(N'msdb.dbo.log_shipping_primary_secondaries')  
    if @object_id is not null  
    begin  
        --  key constraint  
        if not exists (select * from msdb.sys.key_constraints where parent_object_id = @object_id  
                            and name = N'pklsprimary_secondaries')  
        begin  
            alter table msdb.dbo.log_shipping_primary_secondaries  
                add constraint pklsprimary_secondaries  
                    primary key (primary_id, secondary_server, secondary_database)  
        end  
        --  indices  
        if not exists (select * from msdb.sys.indexes where object_id = @object_id  
                            and name = N'nc1lsprimary_secondaries')  
        begin  
            create nonclustered index nc1lsprimary_secondaries   
                on msdb.dbo.log_shipping_primary_secondaries (primary_id)  
        end  
    end  
  
    select @object_id = object_id(N'msdb.dbo.log_shipping_monitor_primary')  
    if @object_id is not null  
    begin  
        --  indices  
        if not exists (select * from msdb.sys.indexes where object_id = @object_id  
                            and name = N'uc1lsmonitor_primary')  
        begin  
            create unique nonclustered index uc1lsmonitor_primary   
                on msdb.dbo.log_shipping_monitor_primary (primary_server, primary_database)  
        end  
    end  
  
    select @object_id = object_id(N'msdb.dbo.log_shipping_monitor_history_detail')  
    if @object_id is not null  
    begin  
        --  indices  
        if exists (select * from msdb.sys.indexes where object_id = @object_id  
                            and name = N'nc1lsmonitor_history_detail')  
        begin  
            drop index nc1lsmonitor_history_detail   
                on msdb.dbo.log_shipping_monitor_history_detail  
        end  
        if not exists (select * from msdb.sys.indexes where object_id = @object_id  
                            and name = N'c1lsmonitor_history_detail')  
        begin  
            create clustered index c1lsmonitor_history_detail   
                on msdb.dbo.log_shipping_monitor_history_detail (agent_id, agent_type)  
        end  
        if not exists (select * from msdb.sys.indexes where object_id = @object_id  
                            and name = N'nc2lsmonitor_history_detail')  
        begin  
            create nonclustered index nc2lsmonitor_history_detail   
                on msdb.dbo.log_shipping_monitor_history_detail (database_name)  
        end  
        if not exists (select * from msdb.sys.indexes where object_id = @object_id  
                            and name = N'nc3lsmonitor_history_detail')  
        begin  
            create nonclustered index nc3lsmonitor_history_detail   
                on msdb.dbo.log_shipping_monitor_history_detail (log_time_utc)  
        end  
    end  
  
    select @object_id = object_id(N'msdb.dbo.log_shipping_monitor_error_detail')  
    if @object_id is not null  
    begin  
        --  indices  
        if exists (select * from msdb.sys.indexes where object_id = @object_id  
                            and name = N'nc1lsmonitor_error_detail')  
        begin  
            drop index nc1lsmonitor_error_detail   
                on msdb.dbo.log_shipping_monitor_error_detail  
        end  
        if not exists (select * from msdb.sys.indexes where object_id = @object_id  
                            and name = N'c1lsmonitor_error_detail')  
        begin  
            create clustered index c1lsmonitor_error_detail   
                on msdb.dbo.log_shipping_monitor_error_detail (agent_id, agent_type)  
        end  
        if not exists (select * from msdb.sys.indexes where object_id = @object_id  
                            and name = N'nc2lsmonitor_error_detail')  
        begin  
            create nonclustered index nc2lsmonitor_error_detail   
                on msdb.dbo.log_shipping_monitor_error_detail (database_name)  
        end  
        if not exists (select * from msdb.sys.indexes where object_id = @object_id  
                            and name = N'nc3lsmonitor_error_detail')  
        begin  
            create nonclustered index nc3lsmonitor_error_detail   
                on msdb.dbo.log_shipping_monitor_error_detail (log_time_utc)  
        end  
    end  
  
    select @object_id = object_id(N'msdb.dbo.log_shipping_secondary')  
    if @object_id is not null  
    begin  
        --  columns  
        if not exists (select * from msdb.sys.columns where object_id = @object_id  
                            and name = N'user_specified_monitor')  
        begin  
            alter table msdb.dbo.log_shipping_secondary  
                add user_specified_monitor bit null  
        end  
        --  indices  
        if not exists (select * from msdb.sys.indexes where object_id = @object_id  
                            and name = N'uc1lssecondary')  
        begin  
            create unique nonclustered index uc1lssecondary   
                on msdb.dbo.log_shipping_secondary (primary_server, primary_database)  
        end  
        if not exists (select * from msdb.sys.indexes where object_id = @object_id  
                            and name = N'nc1lssecondary')  
        begin  
            create nonclustered index nc1lssecondary   
                on msdb.dbo.log_shipping_secondary (monitor_server)  
        end  
        if not exists (select * from msdb.sys.indexes where object_id = @object_id  
                            and name = N'nc2lssecondary')  
        begin  
            create nonclustered index nc2lssecondary   
                on msdb.dbo.log_shipping_secondary (user_specified_monitor)  
        end  
    end  
  
    select @object_id = object_id(N'msdb.dbo.log_shipping_secondary_databases')  
    if @object_id is not null  
    begin  
        --  indices  
        if not exists (select * from msdb.sys.indexes where object_id = @object_id  
                            and name = N'nc1lssecondary_databases')  
        begin  
            create nonclustered index nc1lssecondary_databases   
                on msdb.dbo.log_shipping_secondary_databases (secondary_id)  
        end  
    end  
  
    select @object_id = object_id(N'msdb.dbo.log_shipping_monitor_secondary')  
    if @object_id is not null  
    begin  
        --  columns  
        if not exists (select * from msdb.sys.columns where object_id = @object_id  
                            and name = N'last_restored_latency')  
        begin  
            alter table msdb.dbo.log_shipping_monitor_secondary  
                add last_restored_latency int null  
        end  
        --  key constraint  
        if not exists (select * from msdb.sys.key_constraints where parent_object_id = @object_id  
                            and name = N'pklsmonitor_secondary')  
        begin  
            alter table msdb.dbo.log_shipping_monitor_secondary  
                add constraint pklsmonitor_secondary  
                    primary key (secondary_id, secondary_database)  
        end  
        --  indices  
        if not exists (select * from msdb.sys.indexes where object_id = @object_id  
                            and name = N'uc1lsmonitor_secondary')  
        begin  
            create unique nonclustered index uc1lsmonitor_secondary   
                on msdb.dbo.log_shipping_monitor_secondary (secondary_server, secondary_database)  
        end  
        if not exists (select * from msdb.sys.indexes where object_id = @object_id  
                            and name = N'nc1lsmonitor_secondary')  
        begin  
            create nonclustered index nc1lsmonitor_secondary   
                on msdb.dbo.log_shipping_monitor_secondary (secondary_id)  
        end  
        if not exists (select * from msdb.sys.indexes where object_id = @object_id  
                            and name = N'nc2lsmonitor_secondary')  
        begin  
            create nonclustered index nc2lsmonitor_secondary   
                on msdb.dbo.log_shipping_monitor_secondary (restore_threshold)  
        end  
        if not exists (select * from msdb.sys.indexes where object_id = @object_id  
                            and name = N'nc3lsmonitor_secondary')  
        begin  
            create nonclustered index nc3lsmonitor_secondary   
                on msdb.dbo.log_shipping_monitor_secondary (last_restored_latency)  
        end  
    end  
  
    select @object_id = object_id(N'msdb.dbo.log_shipping_monitor_alert')  
    if @object_id is not null  
    begin  
        --  key constraint  
        if not exists (select * from msdb.sys.key_constraints where parent_object_id = @object_id  
                            and type = N'PK')  
        begin  
            alter table msdb.dbo.log_shipping_monitor_alert  
                add primary key (alert_job_id)  
        end  
    end  
    --   
    --  Update the jobstep flag of the LS jobs  
    --   
    update msdb.dbo.sysjobsteps   
    set flags = 32   
    where job_id in (  
                        select backup_job_id   
                        from msdb.dbo.log_shipping_primary_databases  
                        where backup_job_id is not null  
                        union all  
                        select copy_job_id   
                        from msdb.dbo.log_shipping_secondary  
                        where copy_job_id is not null  
                        union all  
                        select restore_job_id   
                        from msdb.dbo.log_shipping_secondary  
                        where restore_job_id is not null  
                        union all  
                        select alert_job_id   
                        from msdb.dbo.log_shipping_monitor_alert  
                        where alert_job_id is not null)  
  
    --   
    --  Update the jobsteps to relfect the new Katmai path to logshipping.exe  
    --   
  
    exec @retcode = sys.sp_MSgetlogshipagentpath @toolpath = @toolpath OUTPUT  
    if @retcode != 0  
    begin  
            raiserror(32018, 16, 2)  
            return 1  
    end  
  
    --  Update backup job steps  
    update msdb.dbo.sysjobsteps  
    set command = stuff(command, 1, patindex(N'%-backup%', lower(command))-1, N'"' + @toolpath + N'sqllogship.exe" ')  
    where patindex(N'%-backup%', lower(command)) != 0  
        and patindex(N'%' + lower(@toolpath) + N'%', lower(command)) = 0  
        and patindex(N'%sqllogship.exe%', lower(command)) != 0  
        and job_id in (  
            select backup_job_id   
                        from msdb.dbo.log_shipping_primary_databases  
                        where backup_job_id is not null )  
  
    --  Update copy job steps  
    update msdb.dbo.sysjobsteps  
    set command = stuff(command, 1, patindex(N'%-copy%', lower(command))-1, N'"' + @toolpath + N'sqllogship.exe" ')  
    where patindex(N'%-copy%', lower(command)) != 0  
        and patindex(N'%' + lower(@toolpath) + N'%', lower(command)) = 0  
        and patindex(N'%sqllogship.exe%', lower(command)) != 0  
        and job_id in (  
            select copy_job_id   
                        from msdb.dbo.log_shipping_secondary  
                        where copy_job_id is not null )  
          
  
    --  Update restore job steps  
    update msdb.dbo.sysjobsteps  
    set command = stuff(command, 1, patindex(N'%-restore%', lower(command))-1, N'"' + @toolpath + N'sqllogship.exe" ')  
    where patindex(N'%-restore%', lower(command)) != 0  
        and patindex(N'%' + lower(@toolpath) + N'%', lower(command)) = 0  
        and patindex(N'%sqllogship.exe%', lower(command)) != 0  
        and job_id in (  
            select restore_job_id   
                        from msdb.dbo.log_shipping_secondary  
                        where restore_job_id is not null )  
          
      
    --   
    --  all done  
    --   
    return 0  
end