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)MicrosoftVisualStudiov8.0
TeamDataMicrosoft.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)MicrosoftVisualStudiov8.0
TeamDataMicrosoft.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 
8XmlSchemas1033MSBuildMicrosoft.VisualStudio.TeamSystem.Data.PowerTools.Tasks.xsd

Advertisements

8 Responses to Automatically Compare Data and Schema Using MSBuild and Data Dude

  1. maord says:

    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

  2. maord says:

    This comment originally written by:AshishHow to integrate it with  Nant???

  3. maord says:

    This comment originally written by:Maor DavidLast 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.

  4. maord says:

    This comment originally written by:Maor DavidLast 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.

  5. maord says:

    This comment originally written by:Maor David-PurNot 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"

  6. maord says:

    This comment originally written by:Yosi TaguriThanks,
    Waiting….

  7. maord says:

    This comment originally written by:Maor David-PurI 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!

  8. maord says:

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: