6

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

runxc1 Bret Ferrier
  • 8,096
  • 14
  • 61
  • 100

9 Answers9

6

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.

Galwegian
  • 41,475
  • 16
  • 112
  • 158
  • 1
    +1 for SQL Compare - wish I could afford it. –  Nov 12 '09 at 16:38
  • Open DBDiff looks nice but I already have my Diff files I just need something to automatically run them for me. – runxc1 Bret Ferrier Nov 12 '09 at 17:03
  • 1
    As much as I love RedGate's tools, I've got issues with SqlCompare - it tends to drop and re-create indexes on tables when you make changes, and it re-creates all indexes on the PRIMARY filegroup, not where they used to be. – Eric Z Beard Nov 13 '09 at 11:16
  • I use this to compare a database at the stage before any changes and after the changes, the "difference" sql script that is generated I email to the client to update their db. Works awesome, doesn't work with sql 2000 btw. – dimitarie Feb 23 '13 at 15:48
2

Try dbdeploy.NET

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

George
  • 7,864
  • 5
  • 29
  • 25
  • And it's "pre-alpha". What does that even mean? –  Nov 12 '09 at 16:39
  • 3
    @Randolph: In human terms, it's still swimming down the tube ;-) – Galwegian Nov 12 '09 at 17:24
  • I've been using this for over a year now with a team of 8 developers and we have successfully migrated over 600 deltas against our production & non-production environments. Don't let the "pre-alpha" part scare you... it's a very simple utility to learn and use. – Saul Dolgin Nov 12 '09 at 23:10
1

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • It does run scripts but does not check for a version at all. It would error out running scripts that have already been ran. – runxc1 Bret Ferrier Nov 12 '09 at 17:02
  • unless you write your SQL script in such a way that they test for something before actually running, e.g. existance of a table before creating that table again. – marc_s Nov 12 '09 at 17:28
1

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.

Eric Z Beard
  • 37,669
  • 27
  • 100
  • 145
  • Eric, What DBMS are you targeting? I have learned that Oracle does an implicit commit whenever you make schema changes but SQL Server on the other hand can roll back schema as well as data changes. The only tool that looks like it would work is dbdeploy.net but is the largest overkill that I have ever seen and would create more work than it saves. I just might roll my own – runxc1 Bret Ferrier Nov 16 '09 at 15:04
  • Well, my migration scripts tend to involve millions of records, so transactions are out - that would lock up the database for way too long. I am using Sql Server. – Eric Z Beard Nov 16 '09 at 18:37
0

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

kristof
  • 52,923
  • 24
  • 87
  • 110
0

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.

JeffO
  • 7,957
  • 3
  • 44
  • 53
  • I might look at something like this. I do need it to somehow check the database version before running the scripts however so it doesn't repeat scripts every time. – runxc1 Bret Ferrier Nov 12 '09 at 17:05
0

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

runxc1 Bret Ferrier
  • 8,096
  • 14
  • 61
  • 100
0

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.

Neil McGuigan
  • 46,580
  • 12
  • 123
  • 152
0

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.

JBrooks
  • 9,901
  • 2
  • 28
  • 32