I wrote a piece on SQL Compare and customizing the Automap feature. This was handy for me in a small project where I encountered drift and needed to fix the production side.
In the article, I don’t map one of the columns in the target. In this case, I’d actually be dropping the target column in SQL Compare. After all, the idea of SQL Compare is that we want to synchronize the databases.
If that’s not correct, then I’d need to do one of two things: add the column in development or add a filter. Filters are a great way to avoid issues in production when development items are in flight or uncertain (I’ve written about those as well).
In this case, if I really needed to keep this column in production, I wouldn’t want to add a filter. Instead, I’d really want to add the column in development. In fact, I’d want to add it in the same place it is (column order) in production, so in Development I’d rebuild the table with the new column, in that order, and then run the Compare again.
Automap can be a great feature, but it can’t know what your intentions are. It can only guess, like a new developer that is working on the system for the first time. SQL Compare needs guidance to do the job properly.
SQL Compare is here to help you, and if you haven’t used it, give it a try. If you’re on a version older than 12, I’d say you should upgrade to 13 immediately. If you work with SQL 2017, then move from SQL Compare 12 to 13 and ensure all your comparisons work with the new features.