Two
different databases – one is local copy before deployment & the other one
is copy of same database after new release.
1. First, 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 databases 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
a 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 the 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 in 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 a screen-shot of schema compared.
Both Data Comparison & Schema Comparison is very easy &
you need no prior SQL experience.
These options come in 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.
