A feature carried over, but improved upon, from Visual Studio Database Projects is the Schema Comparison tool. The tool allows you to compare one database to another, a database to your project (or vice versa). It will also allow you to do comparisons between dacpacs and projects or databases (or them to dacpacs).
Doing one is pretty simple. We’ll keep using the project we’ve been using in previous lessons. For today’s example I’ve done a safe refactor on the JobTitle column in the HumanResources.Employee table, renaming it to JobName. I have also added a table and view to the dbo schema. For your example simply rename something in your project. After you make the changes, don’t publish them! We need something different for this example.
To kick off the schema compare, go to the SQL menu, then pick Schema Compare, New Schema Comparison. The dialog will be mostly empty, we’ll start with the upper portion.
As you see, on the left we hit the drop down and will pick Select Source.
Here you have three choices. The top is to pick a project as your source. The second will let you pick an existing database as a source. The final choice will allow you to pick a dacpac file. For this example, we’ll pick the database that we created from our AdvWorks project, one prior to the changes you just made.
On the right, hit the drop down and for the target pick your AdvWorks project. Now click the Compare button in the upper left. SSDT will do the comparison and populate a dialog with the results.
In the upper half you will see a list of all the changes found. If you click on a change, the Object Definition area in the lower half populates with the code that creates the objects, and highlights the differences. In this example you’ll see that our source system has the JobTitle column, whereas the project on the right has our change to JobName.
The check boxes allow you to select or deselect individual changes. For example, you could go to the upper half which is designated to delete the ArcaneCode table and view and uncheck them. Then when you apply the changes these would be left untouched.
If you have a lot of changes you wish to omit, you can buik apply the exclusion (and likewise the inclusion) of files. Right click on a grouping (here the groups are changes and deletes), in the popup menu you can choose to Include All or Exclude All.
To apply the changes, simply click the Update button.
You can choose how to group the comparisons findings. In the schema comparisons toolbar, you can choose to group by Action (the default), by the Schemas, or by Type (types being tables, views, etc).
You could have a situation that could result in data loss, for example the deletion of a table. By default SSDT will block any changes that will result in data loss in the target. Your target, however, might be a test database in which you don’t care if you lose data. There may also be other options you wish to override.
To see the options for applying updates, click on the gear immediately to the left of the grouping toolbar button.
Here you can see a vast list of options available to you, that will affect the way in which SSDT applies updates to the target. You can see the “Block on possible data loss” option under the mouse, and could uncheck it to force your changes. There’s a lot of options here, so scroll through the list to see what other options you might be interested in.
Between the dropdowns for source and target is a little double arrow symbol. Click it will swap the source and target. Do it now, so the database now becomes the target and the project becomes the source. Now run the Compare again.
You should now see the button between the Update and Options buttons become enabled. This is the Generate Script button, and becomes active when the target is a database.
Click it an a new window will appear with all the T-SQL that will change the target database to make it in sync with the project.
Let me stress something. This is not the way you should apply changes to your databases! The publish feature is the proper way to do that, in it are options to generate incremental updates.
This option is more for times when you have a test database you want to quickly get in sync, but for whatever reasons don’t want to create a full publish profile.
The Schema Comparison tool is surprisingly useful. A true story, I was working on a project one time that had considerable changes to an existing database. It was a short term project, so not a lot of time. In theory the source database was supposed to be left unchanged. Note I say “in theory”.
Bright and early Monday morning the very new to the job DBA comes to us and says “Oh by the way we had some issues over the weekend so I had to apply a bunch of changes.” When we asked for his scripts he just shrugged and said “I didn’t keep any of that junk. The changes are in the database just go get ‘em.” And with that wandered away, presumably to provide more sunshine to the lives of other dev teams.
At one time this would have been a major set-back. Fortunately the Schema Comparison tool quickly brought our project up to date. We were able to see the database changes before we applied them to our project, and in a few cases exclude the automatic changes and instead make them manually in our project.
Play around with the Schema Comparison tool, you can run it without actually applying changes. Knowing how to use it will help you on that fateful day when a new dba spreads a little sunshine into your own life.