Automatic incremental SQL Script generation for incremental, nightly builds when using Team Build in TFS 2008 and Visual Studio 2008 ?

Question

hi all, hope that everybody here is OK.

We are using VS 2008 as development tool, TFS 2008 as version control as well as build automation. Some of our developer use dbpro for databases changes and some use SQL Server management studio.

I am trying to automate build for Web Application built using C# and VB.Net. Our scenario is such that we have a central database to which our web application connects.

Whenever we supply our clients with a new functionality or a bug fix, we supply them incremental builds.

The SQL script is checked into source control for every incremental build when they have made and tested there changes on our central DB Server.

I want to generate Differential script that can be run at the client as an incremental update script. Now to come about it is a problem. Sometimes our developers tend to forget the database change-sets and the script in the source control is missing an SP or a two.

Also, sometimes we need to insert default data into some of the tables that have strict stringent values and not test values. Like a table that contains Services provided by the panel, we add a new service name, signature, credentials and service address, etc etc in the ServiceTable. Besides this many other tables may have test data that may not be needed.

If we use DataCompare, it will generate changeset for required data (important for client to enable certain services) and our test data that was added to the database as a result of our testing of the functionality or bug fix.

Currently i am using SQLSchemaCompareTask (from Visual Studio 2008 Team Database Professional Power Tools API) in the TFSBuild.proj file of the build definition for TFS 2008.

Using SQLSchemaCompareTask, the script generated contains database names like [dbo]. etc which are not desired as the script fails when run against SQL Server 2000 databses (Some of our client still use SQL Server 2000) databases as teh backend of the application.

Also default data can't be generated by this process.

To overcome this problem, i have to come up with a solution that can compare databases and generate script automatically that does not have to be manually reviewed again before being sent to the client.

Please suggest effective methodology of such SQL script generation and suggest whether two different databases may be used or something ? Is there any toolkit or api that can enable build automation for SQL Server databases?

Thank you all.

Regards

Steve

Answer 1

Rather than collating many individual change set scripts (and therefore occasionally missing objects out), why not use schema compare and data compare to create a single script from your database project using a database equivalent to your client's on the target? This should create a script tailored to their requirements.

In data compare you can exclude test data records that you don't want pushed to your client by unchecking them in the lower grid.

Answer 2

Try to use SQL Examiner Suite for this: http://www.sqlaccessories.com/SQL_Examiner_Suite/

The tool compares both schema and data and produces synchronization scrips (or differentials scripts). You can automate script creation with supplied command-line tool.