Introduction
In this article, we will discuss how to compare two database schema step by step and finally how to update the target database to the server.
Background
Many times, we come across situations where there are multiple databases used for development and they are to be brought in sync when the development changes are to be moved to QA/production.
Schema compare tool makes it easy to identify the inconsistencies between the databases. The tool not only highlights the attributes that were modified, but also the exact lines of code that were modified/updated.
Using the Code
Let us open the VS2012 IDE, Go to SQL -> Schema Compare -> New Schema Comparison to open the Schema comparison wizard.
The schema compare takes two inputs:
- Source: The DB where the changes have already been made and from where the modifications are to be rolled out [usually development DB]
- Target: The DB that is outdated and where the changes are to be made [usually the production or QA database]
Once we have selected the source, target DB. Click on Compare.
Now we can see the changes in the procedures, schema, table are grouped under Change, and the new added tables, procedures are grouped under Add.
Now once we click on any change, we can see the SQL query changes in the object definitions. Changes are highlighted in different color. We can copy/paste modify the query as we want in the target database.
Whereas in add type, we can click on add symbol. Once you have updated the target script, click on update. A pop up will be shown as below:
Once you click on yes, all changes will be reflected in target DB. Now you can view the script, progress, message as shown in the below image:
Points of Interest
There have been lots of changes when compared to VS2010 schema compare on UI side as well as functionality side, most of it being user friendly.
History
- 04 March 2013: First version
