Remove Article Without Reinitialization

Question

Is there any way to remove an article from transactional Replication without a reinitialization?

Answer 1

Hope it may help :-

1) sp_dropsubscription to remove the subscription for individual article.

EXEC sys.sp_dropsubscription
    @publication = '<PublicationName>',
    @article = '<ArticleneedtoDrop>', 
    @subscriber = '<SubscribingServer>', 
    @destination_db = '<DestinationDatabase>'

2) drop the article from the publication without invalidating the snapshot.

EXEC sys.sp_droparticle @publication = '<PublicationName>',
    @article = '<ArticleneedtoDrop>',
    @force_invalidate_snapshot = 0

  

---

Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

Answer 2

Rohit is correct, this will remove the article from the publication without requiring a reinitialization.  Keep in mind if you also need to remove the object from the publication and/or subscription databases you will need to execute DROP <object> statement(s) after executing sp_dropsubscription and sp_droparticle.

---

Brandon Williams (blog | linkedin)

Answer 3

Hi Rohit,

You Can also do the same using the SSMS. Proceed as follows:-

SSMS>Replication Folder>Specific Publication>Right click on it and select properties>Choose Articles Page.

It will populate list of articles on right side. Just uncheck the article from this list which you now you want to remove.

However as Brandon mentioned, need to remove that article from subscriber as well.

Happy troubleshooting

With regards,

Ankur Arora

-----------------

Please mark as answer if it is helpful for you..Thanks

Answer 4

Rohit,,

I finally got the chance to test your suggested scripts on a Sandbox server.   Step 1 ran fine but I received this error when I ran step 2

Msg 20607, Level 16, State 1, Procedure sp_MSreinit_article, Line 99
Cannot make the change because a snapshot is already generated. Set @force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot.

but I don't want to reinitialize. 

Answer 5

can you post your publication script here? I suspect you have a setting in place which prevents you from being able to drop an article without re-initialization.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 6

Here is my exported script. the object names have been altered to protect the innocent. 

-- Enabling the replication database
use master
exec sp_replicationdboption @dbname = N'FisherKing', @optname = N'publish', @value = N'true'
GO

exec [FisherKing].sys.sp_addlogreader_agent @job_login = null, @job_password = null, @publisher_security_mode = 1
GO
exec [FisherKing].sys.sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher = 1
GO
-- Adding the transactional publication
use [FisherKing]
exec sp_addpublication @publication = N'FisherKing_Copyication', @description = N'Transactional publication of database ''FisherKing'' from Publisher ''DDBBServer1\DUB''.', @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'FisherKing_Copyication', @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 = null, @job_password = null, @publisher_security_mode = 1
exec sp_grant_publication_access @publication = N'FisherKing_Copyication', @login = N'sa'
GO
exec sp_grant_publication_access @publication = N'FisherKing_Copyication', @login = N'NT AUTHORITY\SYSTEM'
GO
exec sp_grant_publication_access @publication = N'FisherKing_Copyication', @login = N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @publication = N'FisherKing_Copyication', @login = N'DDBBServer1\SQLServer2005MSSQLUser$DDBBServer1$MIG'
GO
exec sp_grant_publication_access @publication = N'FisherKing_Copyication', @login = N'DDBBServer1\SQLServer2005SQLAgentUser$DDBBServer1$MIG'
GO
exec sp_grant_publication_access @publication = N'FisherKing_Copyication', @login = N'distributor_admin'
GO

-- Adding the transactional articles
use [FisherKing]
exec sp_addarticle @publication = N'FisherKing_Copyication', @article = N'Info_Class', @source_owner = N'dbo', @source_object = N'Info_Class', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Info_Class', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dboInfo_Class]', @del_cmd = N'CALL [sp_MSdel_dboInfo_Class]', @upd_cmd = N'SCALL [sp_MSupd_dboInfo_Class]'
GO
use [FisherKing]
exec sp_addsubscription @publication = N'FisherKing_Copyication', @subscriber = N'DDBBServer1\DUB', @destination_db = N'FisherKing_Copy', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'Info_Class', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'FisherKing_Copyication', @subscriber = N'DDBBServer1\DUB', @subscriber_db = N'FisherKing_Copy', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'
GO
use [FisherKing]
exec sp_addarticle @publication = N'FisherKing_Copyication', @article = N'Info_Data', @source_owner = N'dbo', @source_object = N'Info_Data', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Info_Data', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dboInfo_Data]', @del_cmd = N'CALL [sp_MSdel_dboInfo_Data]', @upd_cmd = N'SCALL [sp_MSupd_dboInfo_Data]'
GO
use [FisherKing]
exec sp_addsubscription @publication = N'FisherKing_Copyication', @subscriber = N'DDBBServer1\DUB', @destination_db = N'FisherKing_Copy', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'Info_Data', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'FisherKing_Copyication', @subscriber = N'DDBBServer1\DUB', @subscriber_db = N'FisherKing_Copy', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'
GO

Answer 7

This is what you need to do:exec sp_changepublication FisherKing_Copyication,allow_anonymous,false
GO
exec sp_changepublication FisherKing_Copyication,immediate_sync,false
GO
EXEC sp_dropsubscription FisherKing_Copyication,Info_Class, ''DDBBServer1\DUB' ,FisherKing_COPY
GO
EXEC sp_droparticle  FisherKing_Copyication,Info_Class
GO

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941