sql | SQL Server‎ > ‎sqlsrv | T-SQL‎ > ‎

tsql | SQLCMD mode

:setvar login "sa"
:setvar pwd "/'],lp123"
:setvar R201 "TP20F_ANIBAL\SQL08R201"
:setvar ORIGIN "TP20F_ANIBAL"

:CONNECT $(R201) -U $(login) -P $(pwd)

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'repl_pub')
drop database repl_pub
go

create database repl_pub
go

use repl_pub

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pub1_alter]') AND type in (N'U'))
DROP TABLE [dbo].[pub1_alter]
go

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[pub1_non_alter]') AND type in (N'U'))
DROP TABLE [dbo].[pub1_non_alter]
go

CREATE TABLE repl_pub.[dbo].[pub1_alter](
[id] [int] IDENTITY(1,1) primary key,
[value] [nvarchar](50) NULL
)
go

CREATE TABLE repl_pub.[dbo].[pub1_non_alter](
[id] [int] IDENTITY(1,1) primary key,
[value] [nvarchar](50) NULL
)
go

SELECT COUNT(1) '發行端待ALTER資料表筆數' FROM repl_pub.dbo.pub1_alter 
SELECT COUNT(1) '發行端不ALTER資料表筆數' FROM repl_pub.dbo.pub1_non_alter 
go

:setvar login "sa"
:setvar pwd "/'],lp123"
:setvar R201 "TP20F_ANIBAL\SQL08R201"
:setvar ORIGIN "TP20F_ANIBAL"

:CONNECT $(ORIGIN) -U $(login) -P $(pwd)
use master

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'repl_sub')
drop database repl_sub
go

create database repl_sub
go

:setvar login "sa"
:setvar pwd "/'],lp123"
:setvar R201 "TP20F_ANIBAL\SQL08R201"
:setvar ORIGIN "TP20F_ANIBAL"

:CONNECT $(R201) -U $(login) -P $(pwd)

-- 正在啟用複寫資料庫
use master
exec sp_replicationdboption @dbname = N'repl_pub', @optname = N'publish', @value = N'true'
GO

-- 正在加入交易式發行集
use [repl_pub]
exec sp_addpublication 
@publication = N'pub', 
@description = N'來自發行者 ''TP20F_ANIBAL\SQL08R201'' 的資料庫 ''repl_pub'' 交易式發行集。', 
@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, 
@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', 
@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'pub', 
@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'sa', 
@publisher_password = N'/''],lp123'

exec sp_addarticle 
@publication = N'pub', 
@article = N'pub1_alter', 
@source_owner = N'dbo', 
@source_object = N'pub1_alter', 
@type = N'logbased', 
@description = null, 
@creation_script = null, 
@pre_creation_cmd = N'drop', 
@schema_option = 0x000000000803509F, 
@identityrangemanagementoption = N'manual', 
@destination_table = N'pub1_alter', 
@destination_owner = N'dbo', 
@vertical_partition = N'false', 
@ins_cmd = N'CALL sp_MSins_dbopub1_alter', 
@del_cmd = N'CALL sp_MSdel_dbopub1_alter', 
@upd_cmd = N'CALL sp_MSupd_dbopub1_alter'
GO

exec sp_addarticle 
@publication = N'pub', 
@article = N'pub1_non_alter', 
@source_owner = N'dbo', 
@source_object = N'pub1_non_alter', 
@type = N'logbased', 
@description = null, 
@creation_script = null, 
@pre_creation_cmd = N'drop', 
@schema_option = 0x000000000803509F, 
@identityrangemanagementoption = N'manual', 
@destination_table = N'pub1_non_alter', 
@destination_owner = N'dbo', 
@vertical_partition = N'false', 
@ins_cmd = N'CALL sp_MSins_dbopub1_non_alter', 
@del_cmd = N'CALL sp_MSdel_dbopub1_non_alter', 
@upd_cmd = N'CALL sp_MSupd_dbopub1_non_alter'
GO

:setvar login "sa"
:setvar pwd "/'],lp123"
:setvar R201 "TP20F_ANIBAL\SQL08R201"
:setvar ORIGIN "TP20F_ANIBAL"

:CONNECT $(R201) -U $(login) -P $(pwd)
use [repl_pub]

exec sp_addsubscription 
@publication = N'pub', 
@subscriber = N'TP20F_ANIBAL', 
@destination_db = N'repl_sub', 
@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'pub', 
@subscriber = N'TP20F_ANIBAL', 
@subscriber_db = N'repl_sub', 
@job_login = null, @job_password = null, 
@subscriber_security_mode = 0, 
@subscriber_login = N'sa', 
@subscriber_password = N'/''],lp123', 
@frequency_type = 4, 
@frequency_interval = 1, 
@frequency_relative_interval = 1, 
@frequency_recurrence_factor = 1, 
@frequency_subday = 2, 
@frequency_subday_interval = 10, 
@active_start_time_of_day = 0, 
@active_end_time_of_day = 235959, 
@active_start_date = 20121004, 
@active_end_date = 99991231, 
@enabled_for_syncmgr = N'False', 
@dts_package_location = N'Distributor'
GO

sp_startpublication_snapshot 
@publication = 'pub'
go

sp_reinitsubscription 
@publication = N'pub', 
@subscriber = N'TP20F_ANIBAL', 
@destination_db = N'repl_sub'
go

WAITFOR DELAY '00:00:07';
go

--RAISERROR ('Error', 16, 1);

Comments