SQL SERVER 复制之使用代码创建发布订阅

 创建复制的时候，坑能需要选择的项目比较多，手动添加会很麻烦，下面收集的示例是用脚本创建发布，添加项目，创建订阅。

 

 --1.创建发布和快照作业
use [Searchoffer2010]
--创建发布
exec sp_addpublication @publication = N'SearchOffer2010_31', @description = N'来自数据库“Searchoff2010”的事务发布。', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
--创建快照作业
exec sp_addpublication_snapshot @publication = N'SearchOffer2010_31', @frequency_type = 1, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 0, @publisher_login = N'repl_user', @publisher_password = N'test'
GO

 

--2．在指定的发布下添加发布项：(对同一发布添加项目后，需再次执行添加订阅步骤，即第3步，才能生成快照(增量))
use searchoffer2010
declare @str nvarchar(100),@sql varchar(8000),@pub Nvarchar(100)
declare c cursor fast_forward for Select name from sys.sysobjects where type='u'
--select 'Bizoffersearchby'+id from searchoffer2010.dbo.base
--where x % 4= 3 and id not in ('0042','007E','001A','0173','00EF','00EB')  and id>'03e3'
--order by id
set @pub='SearchOffer2010_31' --发布名称
open c
fetch next from c into @str
while @@fetch_status=0
begin
  set @sql='exec sp_addarticle @publication = N'+''''+@pub+''''+', @article = N'+''''+@str+''''+', @source_owner = N''dbo'', @source_object = N'+''''+@str+''''+', @type = N''logbased'', @description = null, @creation_script = null, @pre_creation_cmd = N''DROP'', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N''manual'', @destination_table = N'+''''+@str+''''+', @destination_owner = N''dbo'', @vertical_partition = N''false'', @ins_cmd = N'+''''+'CALL sp_MSins_dbo'+@str+''''+', @del_cmd = N'+''''+'CALL sp_MSdel_dbo'+@str+''''+', @upd_cmd = N'+''''+'SCALL sp_MSupd_dbo'+@str+''''
  print @sql
  --exec(@sql)
  fetch next from c into @str
end
close c
deallocate c

--3. 添加订阅
use [searchoffer2010]
go
exec sp_addsubscription @publication = N'SearchOffer2010_31', @subscriber = N'DB192168001043', @destination_db = N'SearchOffer2010', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'SearchOffer2010_31', @subscriber = N'DB192168001043', @subscriber_db = N'SearchOffer2010', @job_login = null, @job_password = null, @subscriber_security_mode = 0, @subscriber_login = N'repl_user', @subscriber_password = N'test', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20090901, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
exec sp_addsubscription @publication = N'SearchOffer2010_31', @subscriber = N'DB192168001044', @destination_db = N'SearchOffer2010', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'SearchOffer2010_31', @subscriber = N'DB192168001044', @subscriber_db = N'SearchOffer2010', @job_login = null, @job_password = null, @subscriber_security_mode = 0, @subscriber_login = N'repl_user', @subscriber_password = N'test', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20090901, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
exec sp_addsubscription @publication = N'SearchOffer2010_31', @subscriber = N'DB192168001045', @destination_db = N'SearchOffer2010', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'SearchOffer2010_31', @subscriber = N'DB192168001045', @subscriber_db = N'SearchOffer2010', @job_login = null, @job_password = null, @subscriber_security_mode = 0, @subscriber_login = N'repl_user', @subscriber_password = N'test', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20090901, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
exec sp_addsubscription @publication = N'SearchOffer2010_31', @subscriber = N'DB192168001046', @destination_db = N'SearchOffer2010', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'SearchOffer2010_31', @subscriber = N'DB192168001046', @subscriber_db = N'SearchOffer2010', @job_login = null, @job_password = null, @subscriber_security_mode = 0, @subscriber_login = N'repl_user', @subscriber_password = N'test', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20090901, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
exec sp_addsubscription @publication = N'SearchOffer2010_31', @subscriber = N'DB192168001030', @destination_db = N'SearchOffer2010', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'SearchOffer2010_31', @subscriber = N'DB192168001030', @subscriber_db = N'SearchOffer2010', @job_login = null, @job_password = null, @subscriber_security_mode = 0, @subscriber_login = N'repl_user', @subscriber_password = N'test', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20090901, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
exec sp_addsubscription @publication = N'SearchOffer2010_31', @subscriber = N'DB192168001032', @destination_db = N'SearchOffer2010', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'SearchOffer2010_31', @subscriber = N'DB192168001032', @subscriber_db = N'SearchOffer2010', @job_login = null, @job_password = null, @subscriber_security_mode = 0, @subscriber_login = N'repl_user', @subscriber_password = N'test', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20090901, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO