The question:
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?
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
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.
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