How to not go mad with schema compare in SSDT

SSDT (SQL Server Data Tools) is really great to manage database schema changes. However, due to a disappeared feature, SSDT can make you go mad very easily.

The schema compare has a lot of configurations. A really lot of configurations. You start the schema compare using the right-click on the SSDT project. Before starting the compare, you need to set the compare configurations. If you forget to tune the configurations in the way you like, you will end up with a script droping or creating objects that you never had intention to change, such as SQL Server logins or user permissions on the database.

You click the settings button to open the configuration screen. You will choose which objects will be part of the compare and which ones will not. There are also many comparison rules about what the script can or can’t do, such as delete your data.

schema settings

Compare Options

 

In the past, it was easy to deal with these configurations: Inside the Visual Studio options (menu Tools -> Options) you could select the default configuration options you would like to use for schema compare and that’s it, you would have always the same options.

old screen

Very few people know why the option to define the default configuration disappeared after Visual Studio 2010. They would need to kill us if we discover. It’s just not there anymore, so everytime you start a new schema compare you need to use the settings button to configure all the options again and again and again.

However, there is a simple and interesting way to avoid this: If you make the configuration once and, instead of making the comparison, you save the schema compare, you can use the saved file as a template.

You will need to edit the saved file and remove the source and target information.

schema compare file

As a result, you end up with a file containing all the options you choosed for the schema compare. From this point, you never again starts a schema compare from the right-click on the SSDT project. When you want to start a new schema compare, you open the default configuration files. All the options will be already set for you, allowing you to keep your mental health.