Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_upgrade_log_shipping

  No additional text.


Syntax
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
    -- Standard, Developer, Enterprise 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

 
Last revision 2008RTM
News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash