How to update Sql server using visual studio database project

In this post I will discuss wonderful SqlCompare tool available in Visual Studio. This tool is not only helpful in comparing two sql servers but it also helps with comparing schema in a SQL server and Sql project in visual studio. Here is a use case that I will explain that triggered use of Compare Schema tool. In my solution I have a database project that is used to update tables, stored procedures and other sql database related activities. I updated a table to assign Identity attribute to a field. When I tried to execute my entity framework code to add some record in to this field, it immediately threw an exception pointing to that field. I realized that although I updated the field in database project in visual studio but the table in sql server was not updated. This means I forgot to execute this update from database project to sql server. Here are the steps that are involved for synchronizing schema between database project and sql server.

• Launch New Schema Comparison from SQL menu item in Visual Studio.

  

  If you do not see SQL menu option in Visual Studio, install appropriate version of Visual Studio to gain access to database related tools.

• Now you will user interface that allows you to select source and target database for comparing schema. For example when you select Select Source option from dropdown list, you will get following dialog box that allows you to select source.

  

  As you can see, you are not limited to choosing a sql server as source or target. You can select a project in your solution as well. Only caveat is that this project should be Database type. Now perform same selection step for target as well.

• Once you have selected source and target schema, click on Compare button. Visual studio will present a nice comparison screen that shows details of what has changed. If new things were added, they will be marked as Add and things that have been modified are marked as Update.

  
  

  If you are ok with the changes, click on Update button in menu. Notice the tool tip on this button. This action will update Target schema only. If you want to switch your action, you will need to switch source and target schema sources.

• Once changes are applied, you will notice that some of the items in your database project in visual studio are marked as checked out and changed. That means you have one last step to perform. Execute Check in process in visual studio.

As you can see how easy it is to synchronize changes between sql server and your database project in visual studio.