Automatically Compare Data and Schema Using MSBuild and Data Dude

VSTS for DB Professionals (aka "Data Dude" or "VSDBPro") provides great tools for schema and data compare.

Like most Visual Studio-based project systems, the core tasks inside the VSDBPro project implemented as MSBuild tasks. The two core activities for Database Projects (.dbproj), “Build” and “Deploy” are implemented by two MSBuild tasks named “SqlBuildTask” and “SqlDeployTask.”

Sometimes, we also need to automate the schema and data compare processes. We can do it with new MSBuild dedicated tasks that shipped with Power Tools for Data Dude:(Currently available for VSTS 2005)

• SqlDataCompareTask: allows you to compare the content of tables within two databases from the command line using MSBuild
• SqlSchemaCompareTask: allows you to compare schemas between two database from the command line using MSBuild

How should you use it?

First, install the Power Tools. Download from here. (notice that the power tools requires Data Dude Service Release 1 installed).

After you installed the power tools you can use the tasks in your MSBuild script.

Example:

  <!--Import the settings-->
  <Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v8.0
 \TeamData\Microsoft.VisualStudio.TeamSystem.Data.PowerTools.Tasks.targets"/>

  <Target Name ="DataCompare">
    <SqlDataCompareTask
       SourceConnectionString="Data Source=(.);Integrated Security=True;Pooling=False"
        SourceDatabaseName="SourceDB"
        TargetConnectionString="Data Source=(.);Integrated Security=True;Pooling=False"
        TargetDatabaseName="TargetDB"
        OutputPath = "$(temp)"
        OutputFileName = "DataCompare.sql"/>
  </Target>

Notice that the task does not allow you to compare against the project right now. Same way you can use the SqlSchemaCompareTask.

<Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v8.0
\TeamData\Microsoft.VisualStudio.TeamSystem.Data.PowerTools.Tasks.targets"/>
<Target Name ="SchemaCompare">
    <SqlSchemaCompareTask
    SourceConnectionString="$(SourceConnectionString)"
    SourceDatabaseName="$(TargetDatabase)"
    TargetConnectionString="$(TargetConnectionString)"
    TargetDatabaseName="$(TargetDatabase)"
    OutputPath = "$(IntermediateOutputPath)"
    OutputFileName = "$(TargetDatabase)SchemaCompare.sql"
    IgnoreChecks ="true"
/>
</Target>

The properties exposed by the MSBuild tasks are documented via an accompanying XSD file located in:

%ProgramFiles%\Microsoft Visual Studio 
8\Xml\Schemas\1033\MSBuild\Microsoft.VisualStudio.TeamSystem.Data.PowerTools.Tasks.xsd