Sometimes changes are made to a shared database that need to be brought into your SQL Project and there are no saved change scripts for those changes. Other times you may just want to see what will change when you publish a project. SSDT has included a Schema Compare option for SQL Projects, dacpac files, and databases. If you are using VS2010, there are two options for SQL and Data.
The “Data” menu is used for the older DB Projects within VS2010. There’s a useful data compare option in there, but the schema compare will not work for SQL 2012 or SQL Projects.
The “SQL” menu contains the Schema Compare item with a sub-item to do a new Schema Comparison
To update your project from a shared database, start a “New Schema Comparison”. You’ll see a screen something like this:
Setting your source/target is pretty straightforward and each will produce a screen something like the following:
You can choose to compare against an open project, a live database, or a Data-tier Application File (dacpac). In our case, we are going to select a live database as the source and our project as the target. Once selected, you may want to change the options to exclude certain object types or ignore certain settings such as file placement. Unlike in VS2010, the options cannot be set to some default at this time. When you’ve set the options to your liking, click the “Compare” button.
If there are any differences, you’ll see a list of them here. The comparison window shows where there are differences between the source and target. This can be really helpful to know whether or not to include this change in the update. To exclude a change, clear the checkbox next to it in the upper section of the window. A green + indicates that this object will be created. A red – indicates that the object will be dropped. The blue pencil indicates that the object will be modified. At this time there is no way to hide any unchecked items that show in the compare. Once you’re satisfied with your selection, click the “Update” button to push those changes into your project.
I’ve been working with SQL Server for quite some time. Along the way, I’ve learned quite a few things and realized that I won’t ever know everything about SQL Server. I intended to keep growing and learning to be able to do my job well and share my experiences with others.
I currently work for a Health-related non-profit based in Boise as a Database Architect.