MSBuild support for Schema Compare is available

Schema compare is one of the most important Visual Studio SQL Server tooling components. As of our July release the schema compare functionality is available via MSBuild. It can be run from the command line or as an integrated part of automated project build systems to detect changes and generate reports.

• SQL Server 2005, 2008, 2008 R2, 2012, 2014, and Microsoft Azure SQL Databases
• Dacpac files targeting SQL Server 2005, 2008, 2008 R2, 2012, 2014, and Microsoft Azure SQL Databases

Schema Comparison requires a supported version of Visual Studio. This version must include the latest version of the SQL Server tooling. Note that this is required in all cases, whether running on a local machine or on a build server.

The core schema compare engine has been totally redesigned in this latest release. One benefit is improved functionality and configurability. MSBuild integration was at the top of our list for new functionality and we’re very happy to add this much-requested feature.

You can now embed schema compare into your daily build process and easily trace your schema changes. Databases and .dacpac files are supported and all the settings and options you are familiar with are included. Two report formats are supported in this version, plain text and XML. The report contains exactly the same information you would see in Visual Studio. You can even customize your report by providing your own XSD when generating an XML report.

Note:

Because Schema Compare is run via MSBuild,  a valid project file is required. This can be a .sqlproj file, in which case all necessary targets will be defined, but can also be any project file as long as it imports the SSDT target file. If you are using a .sqlproj file remember that MSBuild will default to a Visual Studio version associated with the .NET Framework on your machine. If you have both Visual Studio 2012 and Visual Studio 2013 installed, running MSBuild from the command line will default to running the SQL Server Data Tools components installed inside Visual Studio 2012. To overcome this add /p:VisualStudioVersion=12.0 to your MSBuild statement if you wish to run using the SQL Server Data Tools components installed inside the VS2013 install directory. Here is the simplest possible project file you might need. Creating a “MinSchemaCompare.proj” file and copying this into the file allows you to run schema compare against any of the supported targets : 

If you are not working on a SQL Project, make sure you set VisualStudioVersion and have Microsoft.Data.Tools.Schema.SqlTasks.targets imported in your project file. You can find it under %ProgramFiles(x86)%\MSBuild\Microsoft\VisualStudio\{VisualStudioVersion}\SSDT.

Here is what the command looks like:

msbuild "d:\sample.csproj" /t:SqlSchemaCompare /p:source="d:\source.dacpac" /p:target="d:\target.dacpac" /p:XmlOutput="d:\1.xml" /p:XsdPath="d:\SampleXsd.xsd"

This command line compares source and target .dacpac files and generates the comparison result to an XML file based on the XML schema provided.

If you don’t want to write long command line strings (especially when you are comparing databases), you can save a .scmp file and use it in the schema compare MSBuild task as follows

D:\SampleProject > msbuild /t:SqlSchemaCompare /p:SqlScmpFilePath="d:\sc.scmp" /p:XmlOutput="d:\1.xml" /p:Deploy="true"

Notice that schema compare uses default XSD file if the file is not specified.

The settings inside the .scmp file can be overwritten by specifying values from command line.

C:\SampleProject > msbuild /t:SqlSchemaCompare /p:SqlScmpFilePath="d:\sc.scmp" /p:target="d:\target.dacpac" /p:TextOutput="d:\1.out" /p:Deploy="true"

Database: The input is the connection string

.dacpac file: The input is the file location.

There are a lot of comparison and deployment settings. Please see the Default Settings section at the end for a list with default values. Here are how some of the buttons in the Schema Compare tool bar map to command line arguments.

	Parameter	Supported?	Default Value	Note
	Deploy	Yes	False	.dacpac file is not deployable.
	GroupBy	Yes	Action
	Timeout	Yes	5*60*1000 ms (5 minutes)
	ShowEqualObjects	Will be supported in a future release
	ShowUndeployableObjects	Will be supported in a future release

• Plain text output (Indicated by parameter TextOutput)

The default output format for each difference entry (DiffEntry) is: entry name, entry type, source value, update type, target value. To illustrate the output, let’s look at a difference in the Visual Studio UI and see how this will be output by the text formatter:

Here are the comparison result text format output corresponding to what we get from the UI.

by action(Root): NotSpecified Change(Folder): Change Table(TopLevelElement): dbo.Table(Table) Change dbo.Table(Table) Columns(Folder): Delete Column(Element): Delete dbo.Table.c1 Properties(Folder): Delete Collation(Property): Delete null IdentityIncrement(Property): Delete 1 IdentityIsNotForReplication(Property): Delete False IdentitySeed(Property): Delete 1 IsFileStream(Property): Delete False IsIdentity(Property): Delete False IsMax(Property): Delete False IsNullable(Property): Delete True IsRowGuidColumn(Property): Delete False IsSparse(Property): Delete False Length(Property): Delete 0 Precision(Property): Delete 0 Scale(Property): Delete 0

When running from the command line it’s possible to generate extra information that isn't shown in the UI. This is configurable using the following command line parameters:

If you choose to show all information of a DiffEntry, the sequence will be: Default output values, Ordering values, Refactoring values, Inclusion values.

• XML file output (Indicated by parameter XmlOutput)

Unlike plain text format result which is clean but not easy to read, XML format is human readable and easier to manipulate. We provide a default XML schema file as well as Common Type XSD in case you wish to create your own custom report format.

We pre-define a few types corresponding to each DiffEntry information, such as

The schema compare task populates the Inclusion State value when it finds an attribute or element associated with this type. You can find the Pre-defined types in %ProgramFiles(x86)%\MSBuild\Microsoft\VisualStudio\{version}\SSDT\Microsoft.Data.Tools.Tasks.SchemaCompare.CommonTypes.xsd .

At bottom of this file, there are four types you need to extend besides creating a ResultType element.

You have the flexibility to create your own format but you also need to follow some rules. Results have a hierarchical layout format, whether in the UI or when output on the command line. This is something that must be preserved in any customized XSD you create – the XML also needs to have the same hierarchy, which means the root is on top followed by the group, with DiffEntry nested inside the group.

Here is a sample XSD:

<?xml version="1.0" encoding="utf-8"?><xs:schema xmlns:xs="https://www.w3.org/2001/XMLSchema" targetNamespace="https://schemas.microsoft.com/SSDT/SqlTasks/SchemaCompare.xsd" xmlns="https://schemas.microsoft.com/SSDT/SqlTasks/SchemaCompare.xsd"><!--Redefine ResultType, GroupType, ChildrenEntiesType, and DiffEntryType--><xs:redefine schemaLocation="Microsoft.Data.Tools.Tasks.SchemaCompare.CommonTypes.xsd"><xs:complexType name="DiffEntryType"><xs:complexContent><xs:extension base="DiffEntryType"><xs:sequence><xs:element name="MyResult"><xs:complexType><xs:sequence><xs:element name ="MySource" type="SourceValueType"/><xs:element name ="MyTarget" type="TargetValueType"/></xs:sequence></xs:complexType></xs:element><xs:element name="MyOrderChanged" type ="OrderChangedType"/><xs:element name="MyOrdinal" type ="OrdinalType"/><xs:element name="MySiblingOrderChanged" type ="SiblingOrderChangedType"/><xs:element name="MyRefactored" type ="RefactoredType"/><xs:element name="MyChildRefactored" type ="ChildRefactoredType"/><xs:element name="MyInclusionState" type ="InclusionStateType"/><!-- Make sure ChildrenEntiesType element is nested inside a DiffEntryType element, otherwise it will not include all DiffEntry recursively. --><xs:element name="MyChildren" type="ChildrenEntiesType"/></xs:sequence><xs:attribute name="MyUpdate" type="UpdateCategoryType"/><xs:attribute name="MyName" type="DisplayNameType"/><xs:attribute name="MyType" type="EntryTypeType"/></xs:extension></xs:complexContent></xs:complexType><xs:complexType name="ChildrenEntiesType"><xs:complexContent><xs:extension base="ChildrenEntiesType"><xs:sequence><xs:element name="MyEntry" type="DiffEntryType" minOccurs="0" maxOccurs="unbounded"/></xs:sequence></xs:extension></xs:complexContent></xs:complexType><xs:complexType name="GroupType"><xs:complexContent><xs:extension base="GroupType"><xs:sequence><xs:element name="MyEntry" type="DiffEntryType" minOccurs="0" maxOccurs="unbounded"/></xs:sequence><xs:attribute name="Value" type="GroupByVauleType"/></xs:extension></xs:complexContent></xs:complexType><xs:complexType name="ResultType"><xs:complexContent><xs:extension base="ResultType"><xs:sequence><xs:element name="MyGroup" type="GroupType" minOccurs="0" maxOccurs="unbounded"/></xs:sequence><xs:attribute name="MyGroupBy" type="GroupByCategoryType"/></xs:extension></xs:complexContent></xs:complexType></xs:redefine><xs:element name="Result" type="ResultType"/></xs:schema>

The output looks like:

<Result MyGroupBy="by action"><MyGroup Value="Change"><MyEntry MyUpdate="Change" MyName="Table" MyType="TopLevelElement"><MyResult><MySource>dbo.Table</MySource><MyTarget>dbo.Table</MyTarget></MyResult><MyOrderChanged>False</MyOrderChanged><MyOrdinal>0</MyOrdinal><MyContainsOrderChanged>False</MyContainsOrderChanged><MySiblingOrderChanged>False</MySiblingOrderChanged><MyRefactored>False</MyRefactored><MyChildRefactored>False</MyChildRefactored><MyInclusionState>Included</MyInclusionState><MyChildren><MyEntry MyUpdate="Delete" MyName="Columns" MyType="Folder"><MyResult><MySource /><MyTarget /></MyResult><MyOrderChanged>False</MyOrderChanged><MyOrdinal>2147483647</MyOrdinal><MyContainsOrderChanged>False</MyContainsOrderChanged><MySiblingOrderChanged>False</MySiblingOrderChanged><MyRefactored>False</MyRefactored><MyChildRefactored>False</MyChildRefactored><MyInclusionState>None</MyInclusionState><MyChildren><MyEntry MyUpdate="Delete" MyName="Column" MyType="Element"><MyResult><MySource /><MyTarget>dbo.Table.c1</MyTarget></MyResult> . . . . </MyEntry></MyChildren></MyEntry></MyChildren></MyEntry></MyGroup></Result>

If you want to deploy differences to the target, just use /p:Deploy=”true”. When you deploy from the UI you can choose which differences to deploy. This is also supported from the command line if the SelectedObjectsFilePath property is set. The SelectedObjectsFile looks like:

1 <?xml version="1.0" encoding="utf-8"?> 2 <root> 3 <!-- Include dbo.table1 --> 4 <Set Included="true"> 5 <SelectedItem Type="Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlTable, Microsoft.Data.Tools.Schema.Sql, Version=12.0.0.0 , Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"> 6 <Name>dbo</Name> 7 <Name>table1</Name> 8 </SelectedItem> 9 </Set>10 11 <!-Toggle the inclusion status of dbo.table2 -->12 <Toggle>13 <SelectedItem Type="Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlTable, Microsoft.Data.Tools.Schema.Sql, Version=12.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a">14 <Name>dbo</Name>15 <Name>table2</Name>16 </SelectedItem>17 </Toggle>18 19 </root>20

There is the default XSD for XML output.

• Anonymous
  July 15, 2014
  This looks like a very interesting feature but just curious as to why the lack of script generation support? In many cases it is useful to able to review the actual SQL to be executed before proceeding.

• Anonymous
  July 15, 2014
  Excellent news, I can't wait to take a play. All we need now is support for SSIS and SSAS projects.... (hint hint)

• Anonymous
  July 15, 2014
  Nice feature, although the VS dependency is still there :( We are still forced to have VS (SSDT) installed on our general build server. We have it now and use plain CMD Exec tasks to run silent builds with VS (for all BI project types). Anyway I'm really glad to see MSBuild is on your list. :)

• Anonymous
  July 15, 2014
  This is brilliant. It's exactly the feature that I've been waiting for. I must however agree with Daniel: It would be very useful to just generate the SQL script and not apply the update automatically.

• Anonymous
  July 21, 2014
  I am also voting for generate SQL script functionality. Would be very useful and allow to automatically generate change scripts on commit

• Anonymous
  July 23, 2014
  Why can we not generate the reports from within Visual Studio when running a compare?  I think would be most useful to run a schema compare and review the results in a report format.  Being able to export the results for a team to review, seems like a no-brainer?

• Anonymous
  August 13, 2014
  If you can update the database, why can't you generate the SQL script?

• Anonymous
  September 23, 2014
  I'm not sure if I'm talking about something completely different, but schema compare has been able to generate migration scripts for over a year now?

• Anonymous
  October 23, 2014
  I'm attempting to automate the schema compare. When I do it manually, my source is the database and my target is the database project. As mentioned in this post, a database project is not a valid target, so I generated a .dacpac. When running the msbuild command, if my target is the .dacpac, will setting /p:Deploy="true" update the files in the database project?

• Anonymous
  May 12, 2015
  Tried this, got the error: The target "SqlSchemaCompare" does not exist in the project on deploy. Any ideas?

• Anonymous
  May 31, 2015
  the same with Kevin W error MSB4057: The target "SqlSchemaCompare" does not exist in the project.

• Anonymous
  September 14, 2015
  For anyone got the error: The target "SqlSchemaCompare" does not exist in the project on deploy. You may need to download SSDT first, try this for VS2012: download.microsoft.com/.../SSDTSetup.exe

• Anonymous
  September 20, 2015
  The comment has been removed

• Anonymous
  October 15, 2015
  As per the post below from Dr John T 21 Sep 2015 5:02 AM We are also working with a hybrid database deployment from VSO online, Azure SQL and on premise SQL Server. How do we define the target connection string. I think it would be beneficial for others to clarify these parameters. Server=tcp:YOURDATABASEHOSTNAMEHERE.database.windows.net,1433;Database=YOURDATABASEHERE;User ID=YOUR-USERNAME@YOUR-DB-SERVER-HERE;Password=YOURPWD;Trusted_Connection=False;Encrypt=True;Connection Timeout=30; Also, it isn't clear if you are able to use both .dacpac and connection string in a build or if it has to be like for like. In your example, you use a dacpac location for source and target. Will a scenario work when you have a .dacpac for source and sql connection string for target? Thanks!

• Anonymous
  January 29, 2016
  Awesome feature! Any idea if an option to update de .sqlproj based on a database will be added soon?

• Anonymous
  May 24, 2016
  The comment has been removed

• Anonymous
  May 29, 2016
  Chakra PurseThis crocheted purse is an intermediate level pattern with lots of texture. It was designed by Kim Rutledge for the Naturally Caron website. If you like making coordinating sets, there’s a matching scarf too.

• Anonymous
  June 06, 2016
  The comment has been removed

  • Anonymous
    June 07, 2016
    Hi Ken. Firstly thanks for the feedback here - definitely understand the desire to have this just work with project based source / target. For now, the simplest workaround is: - Copy your scmp file for the project based comparison inside SSDT- Open the new copy. Change the target from your project to the dacpac build by that project- save the scmp file.Now it'll work as expected. Before running the compare task you should run build on the project so that the dacpac is up to date.Lastly it would be fantastic to have a Connect bug tracking your desire for cmdline support for Project based targets so we can assess this on our backlog. We have some work we are in progress on for Schema Compare that should make this all easier, but project support isn't on that list (it still relies on dacpacs, so no project updating for example).Thanks,Kevin
    • Anonymous
      July 04, 2016
      Hi Kevin,Thanks for your response. Following your steps it turns out the only thing that was wrong with my manually-edited version was that the path to the .dacpac file was relative to the location of the .scmp file in my case, but absolute in the generated version.In light of that, I'm not sure how much use this workaround will be for me as I don't think I can assume all devs on our team will have the same directory structure, unless we start requiring everyone to put their project in the same location, use the same folder name etc.I posted the bug, as suggested. Here's the link. Let me know if you think it'd fit better elsewhere:https://connect.microsoft.com/SQLServer/feedback/details/2893547
      • Anonymous
        July 08, 2016
        The comment has been removed
    • Anonymous
      September 15, 2016
      The comment has been removed

• Anonymous
  May 31, 2018
  The comment has been removed