建立与删除复制发布

安顺序执行：

 

创建复制

一、配置远程分发服务器

二、配置发布服务器

三、创建订阅

 

删除复制

一、删除发布

二、删除订阅

三、禁用复制分发

 

强制删除复制

sp_removedbreplication 'database'

从数据库中删除所有复制对象。此存储过程在发布服务器上对发布数据库执行，或者在订阅服务器上对订阅数据库执行。在发布服务器上对发布数据库执行时，将尝试删除与分发服务器和订阅服务器上已发布的数据库相关的对象。

 

一、配置远程分发服务器

/****** 将服务器作为分发服务器安装。脚本日期: 2009-12-11 9:34:23 ******/
use master
--配置分发服务器
exec sp_adddistributor @distributor = N'MARKET_P4116/SQL2005DV2', @password = N'yonggeng'
GO
--创建分发数据库
exec sp_adddistributiondb @database = N'distribution', @data_folder = N'D:/testDenny',
   @log_folder = N'D:/testDenny', @log_file_size = 2, @min_distretention = 0,
  @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO

use [distribution]
--配置复制扩展属性
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
 create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
 EXEC sp_updateextendedproperty N'SnapshotFolder', N'//market_p4116/testDenny/unc', 'user', dbo, 'table', 'UIProperties'
else
 EXEC sp_addextendedproperty N'SnapshotFolder', '//market_p4116/testDenny/unc', 'user', dbo, 'table', 'UIProperties'
GO
--制定发布服务器
exec sp_adddistpublisher @publisher = N'MARKET_P4116', @distribution_db = N'distribution',
  @security_mode = 1, @working_directory = N'//market_p4116/testDenny/unc',
  @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO

二、配置发布服务器

use master
--指定分发服务器
exec sp_adddistributor @distributor = N'MARKET_P4116/sql2005dv2', @password = N'yonggeng'
GO

use [testDenny]
--指定发布数据库
exec sp_replicationdboption @dbname = N'testDenny', @optname = N'publish', @value = N'true'
GO
use [testDenny]
--设置日志读取器代理
exec [testDenny].sys.sp_addlogreader_agent @job_login = N'lane/wang yonggeng', @job_password = null,
  @publisher_security_mode = 1, @job_name = null
GO
-- 添加事务发布
use [testDenny]
exec sp_addpublication @publication = N'pubTest', @description = N'来自发布服务器“MARKET_P4116”的数据库“testDenny”的事务发布。',
  @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true',
  @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true',
     @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @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'true', @allow_sync_tran = N'false',
  @autogen_sync_procs = 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'pubTest', @frequency_type = 1, @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 = 0, @active_end_date = 0, @job_login = N'lane/wang yonggeng',
  @job_password = null, @publisher_security_mode = 1

--添加发布商品目录
use [testDenny]
exec sp_addarticle @publication = N'pubTest', @article = N'testTable', @source_owner = N'dbo',
  @source_object = N'testTable', @type = N'logbased', @description = null, @creation_script = null,
  @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F,
  @identityrangemanagementoption = N'manual', @destination_table = N'testTable',
  @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dbotestTable',
  @del_cmd = N'CALL sp_MSdel_dbotestTable', @upd_cmd = N'SCALL sp_MSupd_dbotestTable'
GO

 

--添加发布商品目录
use [testDenny]
exec sp_addarticle @publication = N'pubTest', @article = N'testTable2', @source_owner = N'dbo',
  @source_object = N'testTable2', @type = N'logbased', @description = null, @creation_script = null,
  @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F,
  @identityrangemanagementoption = N'manual', @destination_table = N'testTable2',
  @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dbotestTable2',
  @del_cmd = N'CALL sp_MSdel_dbotestTable2', @upd_cmd = N'SCALL sp_MSupd_dbotestTable2'
GO

 

use [testDenny]
exec sp_addarticle @publication = N'pubTest', @article = N'testTable2', @source_owner = N'dbo', @source_object = N'testTable2', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'testTable2', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dbotestTable2', @del_cmd = N'CALL sp_MSdel_dbotestTable2', @upd_cmd = N'SCALL sp_MSupd_dbotestTable2'
GO

三、创建订阅

-----------------开始: 要在发布服务器“MARKET_P4116”上运行的脚本-----------------
use [testDenny]
exec sp_addsubscription @publication = N'pubTest', @subscriber = N'MARKET_P4116/sql2005dv3',
  @destination_db = N'asdf', @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'pubTest', @subscriber = N'MARKET_P4116/sql2005dv3',
  @subscriber_db = N'asdf', @job_login = N'lane/wang yonggeng', @job_password = null,
  @subscriber_security_mode = 1, @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 = 20091211, @active_end_date = 99991231, @enabled_for_syncmgr = N'False',
  @dts_package_location = N'Distributor'
GO
-----------------结束: 要在发布服务器“MARKET_P4116”上运行的脚本-----------------