Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrestoredbreplication

  No additional text.


Syntax
create procedure sys.sp_MSrestoredbreplication (
    @srv_orig sysname,
    @db_orig sysname,
    @keep_replication int,
    @perform_upgrade bit,
    @recoveryforklsn varbinary(16) = 0x0
 )
as
begin

    set nocount on

    -- db bits
    declare @db_tranbit int
    declare @db_mergbit int
    declare @db_distbit int
    select @db_tranbit = 1, @db_mergbit = 4, @db_distbit = 16

    declare @repl_installed bit -- repl procs installed flag
    declare @remove_repl bit    -- remove replication flag ; remove on true (1)
    declare @restoreoverride int
    declare @db_curr sysname
    declare @retcode int
    declare @proc nvarchar(255)
    declare @replnick_old binary(6)
    declare @replnick_new binary(6)
    declare @compatlevel int
    declare @srcguid uniqueidentifier
    declare @pubid uniqueidentifier
    declare @prev_pubid uniqueidentifier
    declare @flush_proc nvarchar(300)
    declare @done_proc  nvarchar(300)

    -- Fixup lsns for dist db if needed
    -- This is to make sure that after the restore, the lsns are equal to or larger than
    -- the ones that have been sent to the publishers before the restore.
    -- reuse the procedures used by backup.
    exec @retcode = sys.sp_MSrepl_backup_start
    if @retcode <> 0 or @@error <> 0
        return 0

    exec @retcode = sys.sp_MSrepl_backup_complete
    if @retcode <> 0 or @@error <> 0
        return 0

    -- Support override of replication remove on attach and restore
    SELECT @restoreoverride = 0 -- assume normal remove behavior
    -- use instance specific function
    EXECUTE @retcode = master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE',
                                'SOFTWARE\Microsoft\MSSQLServer\Replication',
                                'RestoreOverride',
                                @param = @restoreoverride OUTPUT,
                                @no_output = 'no_output'

    if ( @@error = 0 ) and ( @retcode = 0 )
    begin
        -- ReplRestoreOverride = 1 = user elects to take no cleanup on restore or attach
        if ( isnull( @restoreoverride, 0 ) = 1 )
            return(0)
    end


    select @remove_repl = 0, @repl_installed = 1

    select @db_curr = db_name()

    -- Consider: Lock database using EXEC %%CurrentDatabase().Lock()??
    -- rmak: Doesn't seem necessary nor is the right thing to do as
    -- %%CurrentDatabase().Lock locks only the current database (master)

    -- check server has replication installed; if not, we cannot remove replication; this should be benign
    if not exists ( select * from master.dbo.MSreplication_options
                        where optname = 'transactional'
                           or optname = 'merge' )
        select @repl_installed = 0

    -- check restore to same server/db backed up
    if (( UPPER(@srv_orig) <> UPPER(@@SERVERNAME) ) or ( @db_orig <> @db_curr )) and @keep_replication = 0
    begin
       select @remove_repl = 1
        -- call repldone before removing the published bit
        IF CONVERT(sysname,DATABASEPROPERTYEX(@db_curr,'status'))  = N'READ_WRITE'
			AND HAS_DBACCESS(@db_curr) = 1
			AND DatabasePropertyEx(@db_curr, N'IsPublished') = 1
			--if cdc is still enabled, don't call sp_repldone
			AND not exists(select * from sys.databases where db_id(@db_curr) = database_id and is_cdc_enabled = 1)
        BEGIN
            SELECT @flush_proc = QUOTENAME(@db_curr) + N'.sys.sp_replflush'
            SELECT @done_proc  = QUOTENAME(@db_curr) + N'.sys.sp_repldone'
            EXEC @flush_proc
            EXEC @done_proc NULL, NULL, 0, 0, 1
            EXEC @flush_proc
        END	

        -- clear category field if we are not going to keep replication
        EXEC %%DatabaseEx(Name = @db_curr).SetPublished(Value = 0)
        EXEC %%DatabaseEx(Name = @db_curr).SetMergePublished(Value = 0)
        EXEC %%DatabaseEx(Name = @db_curr).SetDistributor(Value = 0)
    end

    if @perform_upgrade is NULL
        select @perform_upgrade = 0

    -- determine if an upgrade for repl schema to latest version is needed for tran
    if (object_id(N'syspublications', N'U') is not null)
    begin
        -- upgrade from Sphinx
        if(object_id(N'sysextendedarticlesview', N'V') is null)
	        select @perform_upgrade = 1
	
        if (@remove_repl = 0)
        begin
			--master..sysdatbases.category is bound to be 0 when 70 db is restored since 70 dbtable did not have this field
			--if db has syspublications and if we are trying to keep replication, we need to set category field here

			--in mirroring configuration, published mirroring db may not have the category bit set
			--if principal db was published after mirror had been set up, and failover did not happen even once afterwards
			--and after mirror is torn apart, mirroring db was restored with keep_replication (when principal is gone!)
			--we need to set the category bit here. Note sp_MSpublishdb is not called here, in theory if repl metadata is not
			--complete, we should have it go through the upgrade phase above, instead of patching it everywhere.
            EXEC %%DatabaseEx(Name = @db_curr).SetPublished(Value = 1)
			--article cache is in globle memory, if we didn't flush here, may end up with ancient schema!!!
			exec sp_replflush
        end
    end
    -- determine if an upgrade for repl schema to latest version is needed for merge
    if (object_id(N'sysmergepublications', N'U') is not null)
    begin
        -- upgrade from Sphinx
        if (object_id(N'sysmergeextendedarticlesview', N'V') is null)
        begin
              select @perform_upgrade = 1
             	if (@remove_repl = 0)
				begin
					EXEC %%DatabaseEx(Name = @db_curr).SetMergePublished(Value = 1)
				end
        end

        if (@remove_repl = 0)
        begin
        	if exists (select *
			    from dbo.sysmergepublications
			    where publisher_db = db_name() and UPPER(publisher)  = UPPER(publishingservername()))
	
	            EXEC %%DatabaseEx(Name = @db_curr).SetMergePublished(Value = 1)
	
        end
    end

    -- determine if an upgrade for repl schema to latest version is needed for distribution db
    if (	object_id(N'MSrepl_commands', N'U') is not null and
    		object_id(N'MSrepl_transactions', N'U') is not null and
    		object_id(N'MSsnapshot_history', N'U') is not null and
    		object_id(N'MSlogreader_history', N'U') is not null and
    		object_id(N'MSdistribution_history', N'U') is not null and
    		object_id(N'MSsync_state', N'U') is null)
    begin
		-- upgrade from Sphinx
		if (@remove_repl = 0)
		begin
		    EXEC %%DatabaseEx(Name = @db_curr).SetDistributor(Value = 1)
		end
    end

    if (@perform_upgrade = 0) and (
            (object_id(N'syspublications', N'U') is not null
                and not exists (select * from sys.columns where object_id = object_id(N'syspublications') and name = N'allow_initialize_from_backup'))
            or
            (object_id(N'syssubscriptions', N'U') is not null
                and not exists (select * from sys.columns where object_id = object_id(N'syssubscriptions') and name = N'srvname'))
            or
            (object_id(N'sysmergepublications', N'U') is not null
                and not exists (select * from sys.columns where object_id = object_id(N'sysmergepublications') and name = N'use_partition_groups'))
            or
            (object_id(N'MSsubscription_properties', N'U') is not null
                and not exists (select * from sys.columns where object_id = object_id(N'MSsubscription_properties') and name = N'job_step_uid'))
            or
            -- the following is to detect merge Yukon beta2 to beta3 upgrade
            (object_id(N'sysmergepublications', N'U') is not null
                and exists (select * from sys.columns where object_id = object_id(N'sysmergepublications') and name = N'dynamic_snapshot_ready_timeout'))
    		-- we could have added logic to detect shiloh distribution db to yukon here, but it's not a good idea to call sp_vupgrade_distdb
    		-- on non-distdb cuz we don't have all the proper checks there.  Leave it alone for now and handle the oddball after pub/sub
        )
    begin
        -- upgrade from Shiloh/Sphinx
        select @perform_upgrade = 1
    end

	-- perform the upgrade prior to attempting to remove anything
    if @perform_upgrade = 1
    begin
        exec sys.sp_refreshreplsysservers  @mode = 1 -- mode 1 means don't error out if sysreplservers contains a row pointing to remote server
        exec sys.sp_vupgrade_publisherdb @ver_old = 999, @ver_retention = 999 -- retention and old version not relevant on restore

        -- update subscription tables schema only -- skip any recompile of procs, views, etc. -- we will just drop them after restore
        exec sys.sp_vupgrade_MSsubscription_properties
        exec sys.sp_vupgrade_subscription_tables
        exec sys.sp_vupgrade_mergetables @remove_repl = @remove_repl
        exec sys.sp_MSupgrade_subdb_security_metadata
	end
	--two level of dynamic exec,
	--first one is to make sure no-upgrade restore path won't fail with compilation error in case sysreplservers are not there
	--second is to avoid compilation error when coming from 70 (restore leave 70 db cmp level as 70 so collate will fail, but we've already
	--gone through upgrade code path which means sysreplservers should point to @@servername already so this is really no-op
	
	--SD# 962471 in stable_repl_l2 talks about why the fixup here is necessary (SQLBU#358928)
	if exists (select * from sys.tables where name = 'sysreplservers')
	begin
		exec(N'if exists (select * from sysreplservers where srvname <> upper(@@servername))
				exec(N''update sysreplservers set srvname = upper(ss.name collate database_default), srvid = ss.server_id
				from master.sys.servers ss where upper(name collate database_default) = upper(@@servername)
				'')
			')
	end

    -- subscribing dbs are assumed ok

	--take a look and see if this is dist db, and if it needs upgrade
	--no need to upgrade distdb if it's for removal, it contains replication data only, one may want to just load and take a look
	if (object_id(N'MSrepl_commands', N'U') is not null and
		object_id(N'MSrepl_transactions', N'U') is not null and
		object_id(N'MSsnapshot_history', N'U') is not null and
		object_id(N'MSlogreader_history', N'U') is not null and
		object_id(N'MSdistribution_history', N'U') is not null and
		@remove_repl = 0)
	begin
		-- Verify that this SKU is allowed to be a distributor, otherwise unmark the dist bit but leave the db as is
		exec @retcode= sys.sp_MSsku_allows_replication
		if @@error<>0 or @retcode <> 0
		begin
			set @remove_repl = 1
		end
		else
		begin
			EXEC %%DatabaseEx(Name = @db_curr).SetCompatibility(Level = 90)

			--these two checks are done inside sp_dropdistributiondb, if not set properly, user will not be able to drop distribution db
			--via sp_dropdistributiondb,
			if EXISTS
			(
				SELECT	*
				FROM	master.dbo.sysservers
				WHERE	UPPER(datasource) = UPPER(@@SERVERNAME) collate database_default
				  AND	srvstatus & 8 <> 0
			)
			begin
				if EXISTS
				(
					SELECT	*
					FROM	msdb..sysobjects
					WHERE	name = 'MSdistributiondbs' collate database_default
				)
				begin
					if (@remove_repl = 0) and EXISTS
					(
						SELECT	*
						FROM	msdb..MSdistributiondbs
						WHERE	name = @db_curr collate database_default
					)
					begin
					    exec @retcode  = sys.sp_instdist
						if @retcode <> 0 or @@error <> 0
						    return (1)

						exec @retcode = sys.sp_vupgrade_distdb
						if @retcode <> 0 or @@error <> 0
						    return (1)
					end -- end if entry in MSdistributiondbs
					else
						set @remove_repl = 1
				end -- end if MSdistributiondbs exists in msdb
				else
					set @remove_repl = 1
			end  -- end if sysservers set
			else
				set @remove_repl = 1
		end

		if (@remove_repl = 1)
			EXEC %%DatabaseEx(Name = @db_curr).SetDistributor(Value = 0)
			
		--the rest of logic all have to do with pub/mergepub cleanup, skip for now since we think this is distribution db
		return 0
	end

    if ( @repl_installed = 1 ) and ( @remove_repl = 1 )
        exec sys.sp_MSremovedbreplication_internal @dbname = @db_curr, @ignore_distributor = 1, @from_backup = 1

    -- publishing db check : transactional : requires coordinated restore of dist db - no way to check
    if(not exists( select * from master.dbo.sysdatabases
                                    where dbid = db_id(@db_curr)
                                            and category & @db_tranbit = @db_tranbit )
		and @remove_repl = 1) -- @remove_repl is only set to 1 when srv/db is different and keep_replication is not used
    begin
        --backup set contains replication system tables, remove all
        if (object_id(N'syspublications', N'U') is not null)
        begin
            -- publications exist in db, but db on server is not published;
            -- unpublish the db, ignoring distributor cleanup
            EXEC %%DatabaseEx(Name = @db_curr).SetPublished(Value = 1)
            exec sys.sp_MSpublishdb 'false', 1, @from_backup = 1
            EXEC %%DatabaseEx(Name = @db_curr).SetPublished(Value = 0)
            -- we've already brute force stripped replication from the db,
            -- don't call sp_removedbreplication at proc exit
        end
        --backup set does not contain replication system tables, but sys.objects or sys.columns are not in clean state
        --due to whatever failure occurred before, let's clean these bits here so not to block user from dropping the object
        --truncating the log, large text or textptr based operation
        else
        begin
            
            -- cleanup the objects that are still marked by replication bits
            
            exec @retcode = sys.sp_MScleandbobjectsforreplication
            if @retcode <> 0 or @@error <> 0
                return (1)
        end
    end

    --set p2p source db originator_lsn by recoveryforklsn (see VSTS 42847)
	if object_id('MSpeer_lsns') is not null
		update MSpeer_lsns set originator_lsn = @recoveryforklsn where originator_lsn is null or originator_lsn = 0x0


    if (sys.fn_MSrepl_editionid() in (22, 40)) -- Express  or Web
    begin
        -- Drop all merge publications
        if exists(select category from master.dbo.sysdatabases where upper(name) = upper(@db_curr)
        			and category & 4 = 4) and
        	object_id('dbo.sysmergepublications', 'U') is not null
        begin
            raiserror(20731, 10, -1) -- Dropping all merge publications
            begin try
                exec sp_dropmergepublication @publication = N'all', @ignore_distributor = 1
                exec sp_replicationdboption @db_curr, N'merge publish', N'false', 1
            end try
            begin catch
                delete from dbo.sysmergepublications
                	where sys.fn_MSmerge_islocalpubid(pubid) = 1
            end catch
        end
    end

    if 0=@remove_repl and 1=@repl_installed and @db_curr = @db_orig and UPPER(@srv_orig) = UPPER(@@servername)
    begin
        -- the following fixup code needs to be done only when restoring the database on the same server
        -- with the same database name. If being restored under a different database name or on a different
        -- server the tables sysmergesubscriptions and sysmergepublications need to be fixed up before the
        -- fixup below can be done. Such fixup is not needed here because restoring with a different name or
        -- on a different server with replication should only be done to view replication metadata for debugging
        -- purposes. To have a restored database (database restored on to a different server or with a different
        -- name) be used in production there is lot more fixup of replication metadata that needs to be done followed
        -- by the fixup code below.
        if object_id('dbo.sysmergesubscriptions', 'U') is not null
        begin
            -- Check for expired merge subscriptions.
            exec @retcode= sys.sp_MScheckforexpiredmergesubscriptions
                                @mark_expired_as_failed_attach=1
            if @@error<>0 or @retcode<>0 return 1

            -- Create entry with new replica nickname for heavyweight.
            -- All other entries that have the old nickname should also get the new one.
            -- Get existing replnick.
            exec sys.sp_MSgetreplnick @replnick = @replnick_old output

            if @replnick_old is not NULL
            begin
                select @compatlevel= sys.fn_MSgetmaxbackcompatlevel ()

                -- Create a new replnick.
                set @srcguid= newid()
                exec sys.sp_MSgenreplnickname
                            @srcguid= @srcguid,
                            @replnick= @replnick_new output,
                            @compatlevel= @compatlevel

                exec sys.sp_MScreatenewreplnick @replnick_old, @replnick_new
            end

            -- update identity range values to be NULL for all articles that use identity
            -- range. This is needed so that when the merge is run after restore the publisher
            -- will hand out a new range to the subscriber.
            if object_id('MSmerge_identity_range') is not NULL
            begin
                declare @subid uniqueidentifier

                SELECT top 1 @subid = subid from dbo.sysmergesubscriptions
                    where status <> 7 and subid<>pubid and (sys.fn_MSmerge_islocalsubid(subid)=1)
                update dbo.MSmerge_identity_range
                    set range_begin = NULL,
                        range_end = NULL,
                        next_range_begin = NULL,
                        next_range_end = NULL,
                        max_used = NULL
                    where subid = @subid and is_pub_range = 0
                if @@rowcount <> 0
                    exec sys.sp_MScreate_zero_identity_constraint @subid
            end

            -- not needed when upgrading since second phase upgrade will recreate the procs and triggers
            if exists (select * from dbo.sysmergearticles) and @perform_upgrade = 0
            begin
                select @pubid = NULL
                select top 1 @pubid = pubid from dbo.sysmergepublications order by pubid
                while @pubid is not null
                begin
                    exec @retcode = sys.sp_MSregenerate_mergetriggersprocs @pubid=@pubid
                    if @@error <> 0 or @retcode <> 0
                        return 1

                    select @prev_pubid = @pubid
                    select @pubid = NULL

                    select top 1 @pubid = pubid from dbo.sysmergepublications where pubid > @prev_pubid order by pubid
                end
            end
        end
    end
end

 
Last revision 2008RTM
See also

  sp_restoredbreplication (Procedure)
       



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