SSDT as a Database Schema Compare Tool

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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.

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply