Pausing Replication

[cocopud]

We occasionally have to install SQL Scripts to alter tables, etc. and when we do, the scripts will not run because we have some of the tables replicated. Is there an easy way to pause replication so that we can run the scripts and then just restart it without having to redo the replication.

 

[tlbroadbent]

In order to alter a replicated table you have to drop the subscriptions and article, alter the table and add article and subscriptions. You can easily accomplish this with script like this.

USE PublicationDatabase

--Drop Subscription
EXEC DBName.dbo.sp_dropsubscription 'PublicationName','ArticleName','Subscriber','DestinationDBName'

--Drop Article
EXEC DBName.dbo.sp_droparticle 'PublicationName','ArticleName'

--put your code here
ALTER Table Add NewColumn INT NULL

--Add article
EXEC DBName.dbo.sp_addarticle 'PublicationName','ArticleName','SourceTable','DestinationTable'

--Add Subscription
EXEC DBName.dbo.sp_addsubscription 'PublicationName','ArticleName','Subscriber','DestinationDBName'

Terry L. Broadbent - DBA

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents. (Nathaniel Borenstein)