#眉標=SQL Server 2008、Replication、SSMS
#副標=實用SQL Server 2008(7)
#大標=SQL Server 2008交易式複寫(上)
#作者=文／圖 胡百敬



==<box>===========
程式1

--已經將 Northwind 內的資料表設為發行集
--備份發行集資料庫
BACKUP DATABASE Northwind TO DISK='C:\temp\nwind.bak' WITH INIT
GO 
--確認一下要還原的檔案位置擺放
RESTORE FILELISTONLY FROM DISK='C:\temp\nwind.bak'
GO 
--在訂閱端回復資料庫
RESTORE DATABASE nwindSubscribe FROM DISK='C:\temp\nwind.bak'
WITH MOVE 'northwind' TO 'C:\temp\nwind.mdf',
MOVE 'Northwind_log' TO 'C:\temp\nwind_log.ldf'
GO 
USE Northwind
--增加對交易式發行集的 push 訂閱
--會同時增加作業
EXEC sp_addsubscription 
 @publication = 'Customers', 
 @subscriber = 'Byronsub', 
 @destination_db = 'nwindSubscribe', 
 @subscription_type = N'push',
 @sync_type='initialize with backup',
 @backupdevicetype='disk',
 @backupdevicename='C:\temp\nwind.bak' 
==<end>==============




===<反灰>=============
作業'BYRONSUB-Northwind-Customers-BYRONSUB-4'已成功啟動。
警告: distribution代理程式作業已經由隱含方式建立，將於SQL Server Agent服務帳戶中執行。
==<end>==============






==<box>===========
程式2

--建立臨時的資料表，其架構與目標資料表相同
CREATE TABLE dbo.Tmp_Customers
 (…)
ALTER TABLE dbo.Tmp_Customers SET (LOCK_ESCALATION = TABLE)
GO
GRANT DELETE ON dbo.Tmp_Customers TO public  AS dbo
 (…)
--將原始資料表的記錄新增到先前建立的臨時資料表中
IF EXISTS(SELECT * FROM dbo.Customers)
    EXEC('INSERT INTO dbo.Tmp_Customers (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax)
	SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM dbo.Customers WITH (HOLDLOCK TABLOCKX)')
GO

--為刪除原始資料表做準備
--先刪除與該資料表相關的其他設定，例如參照完整性
ALTER TABLE dbo.Orders
	DROP CONSTRAINT FK_Orders_Customers
…
--刪除原始資料表
DROP TABLE dbo.Customers
GO
--將臨時資料表重新命名成目標資料表
EXECUTE sp_rename N'dbo.Tmp_Customers', N'Customers', 'OBJECT' 
GO
--後續的資料表設定，如建立條件約束、索引等
ALTER TABLE dbo.Customers ADD CONSTRAINT
  PK_Customers PRIMARY KEY CLUSTERED 
  (
  CustomerID
  ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
…
==<end>==============





===<反灰>=============
ALTER TABLE Customers 
ALTER COLUMN CompanyName NVARCHAR(50)
==<end>==============




===<反灰>=============
exec sp_helppublication '發行集名稱'
==<end>==============



==<box>===========
程式3

EXEC sp_changepublication
@publication = '發行集',
@property = N'allow_anonymous',
@value = 'false' 
EXEC sp_changepublication
@publication = '發行集',
@property = N'immediate_sync',
@value = 'false' 
==<end>==============




===<反灰>=============
exec sp_addarticle @publication='發行集', @article='發行項',
@source_object='資料表' , @force_invalidate_snapshot=1 
==<end>==============




===<反灰>=============
EXEC sp_addsubscription 
@publication = '發行集',
@article = '發行項',
@subscriber = '伺服器執行個體',
@destination_db = '資料庫',
@reserved='Internal' 
==<end>==============






==<box>===========
程式4

--對資料表刪除索引
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[t]') AND name = N'idxC2')
DROP INDEX [idxC2] ON [dbo].[t] WITH ( ONLINE = OFF )

--建立索引
CREATE NONCLUSTERED INDEX [idxC2] ON [dbo].[t] 
(
  [c2] ASC
) 
==<end>==============




===<反灰>=============
sp_addscriptexec @publication='test',@scriptfile='C:\temp\CreateIndex.sql',@skiperror=0 
==<end>==============