Visual Studio has many hidden gems & most of us usually overlook it until we need in some work/project. One of less used but extremely useful feature is Schema & Data comparison of two databases. Moreover we can generate scripts for updating the schema/data from visual studio itself.
Note: Schema & Data comparison are possible only in Visual Studio Ultimate & Visual Studio Premium and not in most-popular professional edition or free express edition.
Let us see how it works. First I have two different databases – one is local copy before deployment & other one is copy of same database after new release.
1. First of start visual studio, in the main menu, select “SQL,” then “Schema Compare,” then “New Schema Comparison”.
2. This will open a new schema comparison file (.scmp)
3. In the schema compare file we have two drop-down, one for source database & another for destination. Select the two database to be compared.
4. After selecting database you can have various options like filtering based on Object types (Tables, stored procedures keys etc).
5. You can also check various useful options in “General” tab.
6. Now compare the database schema.
You can find objects added, removed or edited and what’s the difference.
In the similar way, you can use Data Comparison:
1. In visual studio, select SQL menu, then Data Compare, then new Data Comparison.
2. In New Data Comparison,
- First select Source Database & Target Database.
- For data comparison, you have following database options for Records to compare:
- Different Records
- Only in Source
- Only in Target
- Identical Records
3. Based on your requirements, select any combination of aforementioned data comparison options.
One very important thing to notice here is to explore the various options available to tweak your comparison. You have different options like:
Most important features are:
- Compare: compares the schema or data. Shortcut is Shift+Alt+C.
- Update: can update changes(schema or data) from source database to destination database. Shortcut is Shift+Alt+U
- Generate Script: can generate script for the difference in schema or data.
- Filter options: can filter results based on object types like Tables, functions, stored procedures etc. This option comes very handy when you want to filter data for some tables in case of data compare and when you want to compare only few stored procedures/any other object type or all stored procedure/ any other object type.
Here is screen-shot of schema compared.
Note: I have deliberately blurred the picture so that actual schema mustn’t be visible.
Both Data Comparison & Schema Comparison is very easy & need no prior SQL experience.
These options comes very handy when you have application development environment on many servers like Development Servers, Staging Servers, QA/ Testing Database & many distributed Production Servers and they need to be synchronized after some builds/releases.
Connoisseur 