How do I safely drop a table under replication

All we need is an easy explanation of the problem, so here it is.

I have newly set up replication on my Sql 2012 Server. It is working OK.

I occasionally have sql scripts that drop existing tables and recreate them.

However, I cannot drop any table currently under replication.

Other than dropping Publication and Distribution, making my table change and then recreating the Publication and Distribution, is there any way to pause or disable Replication so I can make my change and then re-enable Replication?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I don’t believe it is possible to do so in your version of SQL Server other than how you described, or alternatively by removing that specific Table Article from your Publication and then re-adding it when you’re done.

Microsoft released a patch for 2014 and 2016 that let’s you drop a Table that’s being replicated if you specify the allow_drop property on all Publications that replicate that Table Article. This is also available in all subsequent versions of SQL Server as well.

You should consider upgrading versions soon anyway since SQL Server 2012’s extended end of life will end in July 2022. Then it will no longer be officially supported by Microsoft.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply