Database Change Management using hand generated scripts

Question

My need is fairly basic and I don't want to rebuild the wheel. I like to script my database and have written scripts to update it from one version to the next ie 001-create-tables.sql, 002-alter-column.sql etc.

What I want is a simple tool command line or MsBuild that will look at a database see what version the database is at (using some table convention) and run all of the scripts in a folder that have a higher number than the current database version. I would also like the tool to roll back a script if it fails/throws an error and stop at that point.

That is what I would like but I don't mind changing my conventions though I do want to write my own sql scripts. I also want the tool to be free or open source as I don't need too much. As my projects are C# I would prefer the tool be built in .Net

Answer 1

OK so I didn't like any of the options that I found out there. I found some good tools to create the scripts but nothing that would track the version in the database and run the new scripts. Anyway I went ahead and rolled my own Open Source tool to get the job done. If you want to use it you can download if from CodePlex its called KissDB. I also threw up a blog post about it at my blog blog.RunXc

Answer 2

Use liquibase. It's Java though.

Yes, you can write your migration scripts in the SQL variant of your choice.

It is well-tested and used by a lot of people.

Answer 3

Try dbdeploy.NET

http://sourceforge.net/projects/dbdeploy-net/

Answer 4

Have you looked at Open DBDiff yet? It may be a good place to start.

if you change your mind about open source, Red Gate's SQL Compare is the way to go IMO.

Answer 5

There's a pretty interesting project called SQLRunner on SourceForge - it's C#, it's .NET, it's past "pre-alpha" :-)

I've not used it myself, but still - looks quite good, IMHO.

Marc

Answer 6

I you want to write all the scripts yourself you can take advantage of the SQL Server 2005 TableDiff Utility that will give you a lot of flexibility. Some usage examples can be find here

Answer 7

If you have all of your scripts for a given version in a folder, you can run this as a batch file if you place it in that folder:

for %%X in (*.SQL) do SQLCMD -S <SERVER_NAME> -d <DATABASE_NAME> -E -I -i "%%X"  >> ResultBatch.txt

Sorry, I don't remember where I got this from or I would give credit.

Answer 8

This is a tough problem, plain and simple. The tools mentioned in other answers can definitely help, but you're still left to do a lot yourself.

I actually have a table in my schema that stores an ID for each change script (which matches the case number in my issue/bug tracking system). At the end of each change script I insert the ID into that table. I have a separate script that checks to see what's been done on any specific instance of the database. It would be feasible to automate running each of the scripts that haven't been run yet, but I like to run change scripts manually in case anything goes wrong.

Rollbacks can be almost impossible, especially since many schema changes require some sort of data migration. I have found that best practice with any changes to the schema are to make them backwards-compatible. Never rename a column or table (at least at first). Only add things, and make all new adds nullable. A rollback script simply removes the new stuff, if you realize something isn't quite right. Of course you end up with old, unused columns and tables, so you write a second script that is run after your current release is considered stable, which gets rid of the old stuff.

Answer 9

We have our developers check the database change scripts into Subversion. All scripts are repeatable so you can run them multiple times without error. We also link the change scripts to issue items or bug ids so we can hold back a change set if needed. We then have an automated build process that kicks out a single SQL script file with all of the changes sorted appropriately. This single file is then used to promote the changes to the Test, QA and Production environments. We think this is the best approach with enterprise developers. More details on how we do it HERE Your feedback would be appreciated.