maor.Blogging();

Automatically Compare Data and Schema Using MSBuild and Data Dude

Software Development, Team System December 12th, 2007

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

8 Comments to “Automatically Compare Data and Schema Using MSBuild and Data Dude”

maord | June 22nd, 2009 at 23:10

This comment originally written by:
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
maord | June 22nd, 2009 at 23:10

This comment originally written by:Ashish
How to integrate it with Nant???
maord | June 22nd, 2009 at 23:10

This comment originally written by:Maor David
Last week it was exactly one year since I started blogging, so this is my blog first birthday!! Come and read about the blog's statistics, top posts and more.
maord | June 22nd, 2009 at 23:10

This comment originally written by:Maor David
Last week it was exactly one year since I started blogging, so this is my blog first birthday!! Come and read about the blog's statistics, top posts and more.
maord | June 22nd, 2009 at 23:10

This comment originally written by:Maor David-Pur
Not possible. The Power Tools rely on assemblies installed by the Data Dude.

Also I found that this is not covered by the license agreement.

I found at the Data Dude forum that: "in case of Team Build usage, the Team Build license allows you to install DBPro on the team build server, without the need for an additional license, if the server is not being used interactively"
maord | June 22nd, 2009 at 23:10

This comment originally written by:Yosi Taguri
Thanks,

Waiting….
maord | June 22nd, 2009 at 23:10

This comment originally written by:Maor David-Pur
I never checked it. The MSBuild needs the tasks which are an assembly whithin the power tools, so hypotheticaly you can take the assembly and register it in the MSBuild script and use the tasks. But I dont know if there are any dependencies for the assembly. I’ll try it!
maord | June 22nd, 2009 at 23:10

This comment originally written by:Yosi Taguri
Is there a way of doing this without installing DataDude?