交易式複寫:替已經有訂閱的發行集加發行項的 Script

今天有朋友問:新增了資料表發行項,而希望既有的訂閱者只需要加入該資料表,而不是重新完整初始化

可參閱我以前的 blog

交易式複寫:替已經有訂閱的發行集加發行項

感謝小妙提供她的 T-SQL Script

–卸除
SET ANSI_PADDING ON
GO
use [db]
declare @publicationName sysname=N’dbRep_SchemaName’
declare @articleName sysname=N’tblDept’

–卸除對於發行者的特定發行項、發行集或訂閱集的訂閱
exec sp_dropsubscription @publication=@publicationName, @article=@articleName, @subscriber=N’all’

–從快照式或交易式發行集中卸除發行項
exec sp_droparticle @publication=@publicationName, @article =@articleName, @force_invalidate_snapshot = 1
GO

–加入
use [db]
declare @publicationName sysname=N’dbRep_SchemaName’
declare @articleName sysname=N’tblDept’
declare @tableName sysname=N’tblDept’
declare @schemaName sysname=N’SchemaName’
declare @subscriberName sysname=N’SQLInstance’
declare @destinationDbName sysname=N’db’
–建立一個發行項,將它加入發行集中
exec sp_addarticle @publication = @publicationName, @article = @articleName,
@source_owner = @schemaName, @source_object = @tableName , @type = N’logbased’,
@description = null, @creation_script = null, @pre_creation_cmd = N’drop’,
@schema_option = 0x0000004008037CDF, @force_invalidate_snapshot = 1,
@identityrangemanagementoption = N’manual’,
@destination_table = @tableName, @destination_owner = @schemaName,
@vertical_partition = N’false’

EXEC sp_addsubscription
@publication = @publicationName,
@article = @articleName,
@subscriber = @subscriberName,
@destination_db = @destinationDbName,
@reserved= N’Internal’

–用來啟動產生發行集初始快照集的快照集代理程式作業
exec sys.sp_startpublication_snapshot @publication =@publicationName

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: