Home Microsoft SQL Server DigiMailing iRN Contact


  No additional text.

create procedure sys.sp_restoredbreplication (
    @srv_orig sysname,
    @db_orig sysname,
    @keep_replication int = 0, -- Make it int so that we can expand later.
    @perform_upgrade bit = 0, -- server will set this parameter when the database is restored from a previous version of sql server.
    @recoveryforklsn varbinary(16) = 0x0
 * used by restore process to strip out replication settings if restoring to non-originating
 * server/db or system otherwise not capable of keeping replication working
 * WARNING : procs called here run internal to server and must be owner qualified
	declare @retcode int

	select @retcode = 0

    ** Security Check: RESTORE statement requires sysadmin, dbcreator, or
    **                 dbo (THE dbo, not just any member of the db_owner
    **                 role.)
    if (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)
       and (ISNULL(IS_SRVROLEMEMBER('dbcreator'),0) = 0)
       and (ISNULL(IS_MEMBER('db_owner'),0) = 0)
        raiserror(18799, 16, -1)
        return 1

	-- Only attempt to run restore code on REPLICATION scenarios.
	-- this helps us avoid failures when msdb or other dbs are
	-- offline and a user is attempting to restore a non-repl db
	-- 1. Transactional Publisher
	if object_id(N'syspublications', N'U') is not null
		or object_id(N'syspublications', N'V') is not null
	-- 2. Merge Publisher
		or object_id(N'sysmergepublications', N'U') is not null
	-- 3. Distributor
		or (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'MSmerge_history', N'U') is not null)
    -- 4. Transactional Subscriber
    	or object_id(N'MSreplication_subscriptions', 'U') is not null
    -- 5. Merge Subscriber
    	or object_id(N'MSmerge_replinfo', 'U') is not null
  		exec @retcode = sys.sp_MSrestoredbreplication @srv_orig = @srv_orig,
													    @db_orig = @db_orig,
													    @keep_replication = @keep_replication,
													    @perform_upgrade = @perform_upgrade,
													    @recoveryforklsn = @recoveryforklsn
   --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
        -- cleanup the objects that are still marked by replication bits
        -- clear category field if we are not going to keep replication
		declare @db_curr sysname,
                @flush_proc nvarchar(300),
                @done_proc  nvarchar(300)
		select @db_curr = db_name()

        -- 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)
            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

        EXEC %%DatabaseEx(Name = @db_curr).SetPublished(Value = 0)
		EXEC %%DatabaseEx(Name = @db_curr).SetMergePublished(Value = 0)
		EXEC %%DatabaseEx(Name = @db_curr).SetDistributor(Value = 0)
		exec @retcode = sys.sp_MScleandbobjectsforreplication
		if @retcode <> 0 or @@error <> 0
            return (1)
	return @retcode

Last revision 2008RTM
See also

  sp_refreshreplsysservers (Procedure)


  Query a named instance
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
Zoekmachine optimalisatie
SQL Servers hidden objects
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
sIFR; de combinatie tussen HTML en Flash