Automated generation of database change scripts

Question

I'm looking for a way to automated the generation of change scripts for our databases.

Currently we use Visual Studio 2008 database edition, which has the Schema Compare option, but I don't see a way to automated those actions.

What I'd like to do is create an old and a new database during my daily builds and have schema compare generate a change script for the differences between those two. (Differences in schema and procedures, lookup tables can be dropped and recreated without problems)

Does anyone know of a solution for, or am I going to write miles of scripts to get this going?

Using any paid products besides the Microsoft tooling is not an option here...

Update based on feedback:

• I have various databases of various sizes, but all changes will happen in a controlled manner while being aware of data already present.
• Only "easy" changes should be taken care of automagically, i'm thinking addition of columns and tables (very common). Columns may never be removed (so the tool/script may protest if this happens)

Answer 1

You can try reversing your approach a bit.

I'm developing a tool called Wizardby which allows you to write database migrations in a special DSL. Thus, instead of relying on fragile schema comparison algorithms (for example, none of them can handle column/table renames properly), you write all modifications to your database schema in an mdl file, which later gets compiled by Wizardby into platform-specific SQL. It can also be integrated into your build process.

Answer 2

The functionality of SQL Compare is also available as libraries, and documented as such. I utilized those dlls in a console app, had no problems.

Answer 3

Can you be more specific? For example how big is the database? How many data it has? How fast the upgrade should be?

I'm sure there is not a fully generic and automatic solution for this problem.

• If you rename column and do just and only schema compare? The column will be dropped and created empty.

• If you divide column FullName to Name, MidleName, SurName. How the schema compare should deal with it?

Sure this samples can be continued on and on.

Answer 4

There seems to be a SqlSchemaCompareTask in the Microsoft® Visual Studio Team System 2008 Database Edition Power Tools. http://www.microsoft.com/downloads/details.aspx?FamilyID=73ba5038-8e37-4c8e-812b-db14ede2c354&displaylang=en

This is based on the first version of the Database Edition and not on the GDR2 updated release.

Answer 5

As thijs mentioned, the Database Edition Power Tools contains the SqlSchemaCompareTask which you can use from your code to generate a change script:

SqlSchemaCompareTask task = new SqlSchemaCompareTask()
{
    SourceConnectionString = "Data Source=source-db; ...",
    SourceDatabaseName = "source-database-name",
    TargetConnectionString = "Data Source=target-db; ...",
    TargetDatabaseName = "target-database-name",
    OutputFileName = "changes.sql",
    OutputPath = @"C:\path\to\output"
};

task.Execute();

Alternatively, you can execute it using a build script via msbuild.exe:

<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>

Answer 6

Similar to what @Anton Gogolev is doing, we are using a tool that allows you to write migrations in XML files. The tool we use is called Liquibase and it supports many different DBMS flavors. Not only do we use it internally on our developer schemas, we also use it externally during the upgrade installation that the customer runs.

Answer 7

For 10 years now I've used DbGhost to implement sql change management at the majority of the consultancies I've been at; it's a never ending surprise at the lack of SQL change management to rival that provided for application code.

Innovartis' support is second to none in providing assistance at implementing both a continuous integration approach to database support, as well as release process and upgrade paths. Most recently I've applied it to the Monorail / nHibernate project I'm working on that generates upgrade scripts from our POCO domain model that specifies our database. These are created and applied using our CruiseControl build on every check-in.

More specifically for your situation, you can point DbGhost to the release version (v1.0) of your database and script out your source and static data. This can then be entered into source control. These are optional steps but a wise starting point.

DbGhost can also compare (and/or create) either a database generated from source or a current database (say v1.1) and either provide a comparison report, upgrade script or upgrade the target database.

We use it to generate all upgrade scripts (rollforward and rollback) to upgrade between each delta.

In 10 years whenever I've found a problem with the product, the helpful support guys always politely point out either a mistake in my process or one of the many extension points in the process that I need to use to get past my problem. It certainly handles dropping of columns, indexes, data, absolutely everything. The settings allow you to achieve only simple changes automatically and can fail on warnings, or removing objects (columns/tables), all maintainable in xml settings files (we have more sympathetic settings in our development build and more strict for the live build).

I wouldn't contemplate Sql Server development without it and it revolutionised my SQL development.

If you need more detail on a recommended process or more prescriptive steps please let me know.