Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

August 7, 2013

SSDT Schema Compare

As part of the Visual Studio SQL Server Data Tools (SSDT), the Schema Compare option compares the structure of the database tables, stored procedures, views, and other database objects. It shows you how two data sources are the same and how they are different.

See this prior post for more information on SSDT.

Use the Schema Compare option to:

  • Compare one database against another
    • Development vs Test or Test vs Production, for example
  • Compare a database against a SQL Server Database Project
    • Development database vs updated SQL Server Database Project, for example
  • Compare a database against a DACPAC
    • Production database vs packaged schema, for example

In this example, the current task added a DisplayOrder field to the CustomerType table. You want to compare your current development database to the current test database to confirm your changes.

1) SQL | Schema Compare | New Schema Comparison …

image

2) Select the Source

image

For the source, you can select:

  • A SQL Server Database Project from your solution.
  • A SQL Server database.
  • A packaged data-tier application file (DACPAC)

For this example, the current development database is selected.

3) Select the Target

image

You have the same choices for the target:

  • A SQL Server Database Project from your solution.
  • A SQL Server database.
  • A packaged data-tier application file (DACPAC)

For this example, the current test database is selected.

4) Click the Compare button in the Schema Compare toolbar.

image

The top portion of the result displays the changes that were found. Drill down to see the detail. In this example, the schema compare found a table change. Drilling down, you can see that the DisplayOrder column has a "+" action, meaning it was added.

Click on any change to see the Object Definitions, which shows the source and target scripts.

From here you can use the toolbar to perform other actions such as:

  • Click the Update button to update the target from the source.
  • Click the Generate Script button to generate an update script file.
  • Click Options to set options for the script
    • Block on possible data loss is a key option you may want to set or allow depending on the change.

Comparing the current database project to the current Test database yields the same result:

image

Use the schema compare any time you want to compare a database to another database, SQL Server Database Project, or DACPAC (or any other combination of these).

Enjoy!

For more information on this and other Visual Studio 2012 features, see my Pluralsight course: Mastering Visual Studio 2012.

2 Comments

  1.   hdcn — August 8, 2013 @ 3:27 pm    Reply

    May I ask is there any command line tool support this Schema Comparison

  2.   DeborahK — August 8, 2013 @ 4:11 pm    Reply

    Hi hdcn –

    If you create a DACPAC as detailed in an earlier post on my blog, you can use the SqlPackage.exe on the command line. Using the DeployReport option with SqlPackage details the differences between the DACPAC and the target database.

RSS feed for comments on this post. TrackBack URI

Leave a comment

© 2019 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs