Today I want to write about new feature in SSDT (SQL Server Data Tools) to compare two databases in term of structure.
With a free installation of SSDT you can have ability to compare two SQL Server databases and review changes, create changes script and apply it on destination database. there are some options also to empower creation and applying changes script.
In this post you will see how to install SSDT and use it to compare schema of SQL Server databases .
If you don’t have SSDT installed you can download it online and install it for free from here:
Note that To use SSDT as Schema Compare you don’t need to have SQL Server 2012 Database Engine or other features installed, you just need SSDT which can be downloaded or installed from address above.
After installation Open SSDT;
Click on SQL -> Schema Compare -> New Schema Comparison
Select Source and Destination databases (they can be on same server or different servers), you can also choose Data Tier projects .
Click on Compare to compare databases. this is sample of results :
As you see in screenshot above, list of all differences in term of structure will be shown in three major categories; Delete, Change and Add
by clicking on each item you will see script of that item in source and destination databases in two different panels
You can Check the check box in front of each item to add it in change script or uncheck it to remove from change script. After selecting items, you can click on Update button to apply changes to destination databases. result of changes can be viewed in Data Tools Operation window.
SSDT pick items that are selected and create change script based on them, and generate result script, that you can see in screenshot below:
There are bunch of options in generating script which will empower schema compare, you can find all options with clicking on Options in SQL Schema Compare window, as you see in image below there are a lot of options. For example "Block on Possible Data Loss" will prevent changes that may cause data loss on destination database. but if you are aware of changes, and you have a backup of current data in destination and like to apply changes on structure and then re-populate data, then you can un-check this option and then compare schema.
You can also select type of objects that you want to be part of database schema comparison with clicking on Object Types tab in Schema Compare Options window. These sql server objects can be consists of tables, triggers, primary keys and other objects that listed below.
SSDT as Database comparison tool Pros and Cons
SSDT is free you can simply download it and install it and use it.
SSDT is powerful schema comparison tool because of bunch of options which brings a lot of abilities in generating change script
SSDT (2012) is only working with Microsoft SQL Server Databases at the moment.