Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































SQL Schema Comparison with Visual Studio 2010

MSSQLTips author Arshad Ali By:   |   Read Comments (8)   |   Related Tips: More > Comparison - Data and Objects
Problem

Often we need to compare databases across two different environments (or even sometimes in the same environment) to identify the schema object differences, so that one database can be brought in sync with another one. For example, we normally do development on the development box; then during the build preparation we compare the development database with the QA/UAT/Production database to identify differences and generate schema objects for synchronization or incremental deployment scripts. How we can compare schema objects between databases and generate synchronization or incremental deployment scripts?

Solution

Although there are many tools available for schema comparison (some free and some you need to purchase) I am going to discuss the Microsoft Visual Studio Database edition for schema comparison. Microsoft Visual Studio Database edition offers several features for database development, for example you can create an offline representation of a database for database development and version control, Database Unit Testing, Code Analysis, Schema Comparison, Data Comparison etc.

In this demonstration I am going to show you how Schema Comparison works on Visual Studio 2010 Ultimate edition although you can do the same with Visual Studio 2005/2008 Database edition too.

Open the Microsoft Visual Studio IDE (Integrated Development Studio) and you will see a "Data" menu in the menu bar depending on the Microsoft Visual Studio edition you have installed (for more details click here). Select Schema Compare under Data menu and then New Schema Comparison as shown below.

 how Schema Comparison works on Visual Studio 2010 Ultimate edition

You will see a dialog box like this, here you need to specify your source schema and target schema. The source or target could be either a database project (offline representation of your database) or the database itself (for which you need to set up a connection to your SQL Server instance) or a *.dbschema file. You can even save your selection as *.scmp file to use later on.

The source or target could be either a database project (offline representation of your database) or the database itself

The moment you click on the OK button, it will start doing the comparison. You will notice a new tool bar, some of the options of this new tool bar are: you can filter out the comparison result as you can see in the image below, you can synchronize your target by writing updates to it (have a look on the note below before doing this), you can export your synchronization/incremental update script to a file or to the editor.

 notice a new tool bar

The schema comparison result screen will look like the image shown below. On the top pane there are four columns; Status tells the type of change of each object i.e.. if the object is New, Missing, Equal or has a different definition. The next column shows the name of the object at the source. Third column allows you to specify the kind of action which you want to take during synchronization for example if you want to Skip updating target, update target, drop from target if the object is missing in source, create on target if the object does not exist on the target. And finally the fourth column shows the name of the object at the target.

The next pane shows the object definition of the selected object. It marks the changes in different colors, the meaning of the color coding is available on the bottom of this pane as you can notice in the below screenshot.

The bottom pane shows the target schema update script depending on the selection of "Update Action" of each object. If you are not able to see it or want to refresh, click on "Refresh Update Script" icon on the toolbar.

The schema comparison result screen

The Schema Comparison tool allows you to specify the different schema comparison options as well as specify which types of objects are to be compared as shown; make note here by default the Schema Comparison tool ignores extended properties, you can change this default behavior as well in the comparison options:

The Schema Comparison tool allows you to specify the different schema comparison options

review your deployment scripts and take a backup of your target database

Note: As long as your source has new objects there is nothing to be worried about, but if you have renamed your objects or columns at the source you might incur data loss while updating the target. For example in the above schema comparison result image you can see the EmployeeID column has been renamed to BusinessEntityID, so while updating the target it will drop the EmployeeID column and add the BusinessEntityID column which will have no data even though the EmployeeID column has data in it. So it is recommended to review your deployment scripts and take a backup of your target database before updating the target database. You can change this default behavior of data loss in the Schema Compare Options, as discussed above, and check the "Block schema updates if data loss might occur" under Script generation options and then the generated script will look like this if it would cause any data loss.


/*
The column [HumanResources].[Employee].[ContactID] is being dropped, data loss could occur.
The column [HumanResources].[Employee].[EmployeeID] is being dropped, data loss could occur.
The column [HumanResources].[Employee].[ManagerID] is being dropped, data loss could occur.
The column [HumanResources].[Employee].[Title] is being dropped, data loss could occur.
The column [HumanResources].[Employee].[BusinessEntityID] on table [HumanResources].[Employee] must be added, but the column has no default value and does not allow NULL values. If the table contains data, the ALTER script will not work. To avoid this issue, you must add a default value to the column or mark it as allowing NULL values.
The column [HumanResources].[Employee].[JobTitle] on table [HumanResources].[Employee] must be added, but the column has no default value and does not allow NULL values. If the table contains data, the ALTER script will not work. To avoid this issue, you must add a default value to the column or mark it as allowing NULL values.
*/
IF EXISTS (select top 1 1 from [HumanResources].[Employee]) RAISERROR ('Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT
GO
Next Steps


Last Update: 8/12/2010


About the author
MSSQLTips author Arshad Ali
Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Thursday, August 12, 2010 - 7:09:26 AM - Sloop Read The Tip
The screenshots are all broken.  Are the links bad?


Thursday, August 12, 2010 - 8:26:12 AM - admin Read The Tip
This is now fixed.


Tuesday, August 24, 2010 - 9:46:02 AM - Tony Henley Read The Tip
When you say "although you can do the same with Visual Studio 2005/2008 Database edition too.", are you referring to the VS that comes with MS SQL Server 2008? I open up the Visual Studio that comes with SQL Server 2008 and I do not find a DATA menu in the menu bar.


Tuesday, August 24, 2010 - 10:39:18 AM - Arshad Read The Tip
Hi Tony,

Please note the highlighted, "although you can do the same with Visual Studio 2005/2008 Database edition too.". Microsoft Visual Studio comes in several editions and database development feature is available with Database edition/Team Edition/Ultimate etc, kindly follow the links below to get more understanding of this:

http://www.microsoft.com/visualstudio/en-us/products

http://www.microsoft.com/downloads/details.aspx?familyid=727BCFB0-B575-47AB-9FD8-4EE067BB3A37&displaylang=en

http://msdn.microsoft.com/en-us/vstudio/aa700921.aspx

 Hope it clarifies your doubts.

Thanks,

Arshad


Friday, August 27, 2010 - 7:32:44 AM - Jim Curry Read The Tip
What permissions are necessary to compare schemas? Also is there any impact on the network traffic wise?


Friday, August 27, 2010 - 9:03:37 AM - Arshad Read The Tip
Hi Jim,

Here is the details about the permissions required for different database features in Visual Studio.

http://msdn.microsoft.com/en-us/library/aa833413.aspx#SchemaAndDataComparePermissions

Not much but there would difinitely be slight load on network when the comparison data has to travel to client machine from the server.

Thanks,

Arshad


Friday, June 01, 2012 - 11:40:39 AM - Prakash Patel Read The Tip

 

Very good finding.

 

Thanks.


Monday, September 16, 2013 - 8:37:57 AM - ramvarma Read The Tip

Hi,

    I have .dbschema file i am using visual studio studio 2010 i am able to compare schemas of two databases but how can i take report of that changes. In visual studio we have export option but it was disabled when i am using .dbfiles please any one send me the solution

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.