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
