Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

SQL Schema Comparison with Visual Studio 2010


By:   |   Read Comments (8)   |   Related Tips: 1 | 2 | 3 | 4 | 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:





About the author











More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Monday, September 16, 2013 - 8:37:57 AM - ramvarma Back To Top

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

 


Friday, June 01, 2012 - 11:40:39 AM - Prakash Patel Back To Top

 

Very good finding.

 

Thanks.


Friday, August 27, 2010 - 9:03:37 AM - Arshad Back To Top
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, August 27, 2010 - 7:32:44 AM - Jim Curry Back To Top
What permissions are necessary to compare schemas? Also is there any impact on the network traffic wise?


Tuesday, August 24, 2010 - 10:39:18 AM - Arshad Back To Top
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


Tuesday, August 24, 2010 - 9:46:02 AM - Tony Henley Back To Top
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.


Thursday, August 12, 2010 - 8:26:12 AM - admin Back To Top
This is now fixed.


Thursday, August 12, 2010 - 7:09:26 AM - Sloop Back To Top
The screenshots are all broken.  Are the links bad?


Learn more about SQL Server tools