Friday, November 2, 2012

SSDT: Updating by Using Schema Compare

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.
clip_image001
  • 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
clip_image002
To update your project from a shared database, start a "New Schema Comparison". You'll see a screen something like this:
clip_image003
Setting your source/target is pretty straightforward and each will produce a screen something like the following:
clip_image004
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.
clip_image005
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.








4 comments:

  1. Schema Compare is a great SSDT tool. But why it doesn't replace sclcmd variables of linked server or db with it's values in project options?

    ReplyDelete
    Replies
    1. That is a better question for MS who actually designed this. However one thought is that you can do a Schema Compare without actually having a project open. At that point it's a straight schema/object compare, nothing more. The publish options are designed to actually do a variable replace and compare. If you set up your options correctly, you can even set up your projects to do a publish on debug to make things easier. Jamie Thomson mentioned that on his blog recently: http://sqlblog.com/blogs/jamie_thomson/archive/2013/12/19/schema-compare-or-publish-ssdt.aspx

      Delete
  2. Is there a way to do this compare in Visual Studio 2013?

    ReplyDelete
    Replies
    1. Looks like you need to do it within the project itself. If you right-click the project, you should see a "Schema Compare" option. I don't see it anywhere else in the menus. You can also launch it from within the SQL Server Object Explorer.

      Delete