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:
http://msdn.microsoft.com/en-us/data/hh297027
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
Pros:
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
Cons:
SSDT (2012) is only working with Microsoft SQL Server Databases at the moment.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.