11 Tools for Database Versioning

'DTM Schema Comparer' on Thu, 14 Sep 2006 03:44:08 GMT, sez:

it's not bad.

'Dan F' on Thu, 14 Sep 2006 04:16:07 GMT, sez:

We use a homegrown nant script + vault nant tasks + a homegrown exe that uses the redgate libraries to script each database object to a file. It's not perfect (damn thing crashes often) but at least it gives us a once a night checkpoint of the current state of the database world. Couple that with the history views in vault and we can zoom in on changes pretty easily.
On top of that we use the redgate products heavily when rolling patches to clients. I can't believe we ever used to "maintain" databases without them!

'Paul Coddington' on Thu, 14 Sep 2006 04:29:11 GMT, sez:

At present I am working in SQL 2000, so I am using my own generic NAnt scripts calling built-in SQL Server command line tools to dump scripts for each object, clean them up internally, and place them into structured folders with nice filenames for each object.

This works well and cost me nothing but time to implement. It is easily integrated with Subversion and can script and create any number of databases defined within the project folder very quickly at a single click.

DB Ghost is the best of what I have tried of the commercial tools, so far, though it has some annoying glitches - for example, machine names and paths are encoded into the scripts, so you still need NAnt to clean them up to become 'generic' so they will work on each developers local machine (and the server) without alteration.

The overwhelming strength of DB Ghost is, of course, the ability to merge differences into an existing database, but this does not come cheap and is well beyond the budget of IT contractors, such as myself.

Some of the other tools I have tried are woefully incomplete for the hefty prices charged - for example, some only do stored procedures and nothing else. By contrast, my home-brewed script does every object and lets you selectively version data as well. For this reason, I am not impressed with some of the offerings out there.

'Jeff Atwood' on Thu, 14 Sep 2006 05:05:02 GMT, sez:

You forgot the upcoming Visual Studio Team System Database Edition. It does full version control of all database objects *and* it includes schema/data comparison and script generation tools, too.

Oh yeah and you can write unit tests against your database with it.

All in all, SUPER FREAKIN' SWEET.

'lb' on Thu, 14 Sep 2006 05:09:16 GMT, sez:

>Visual Studio Team System Database Edition

something else to try! i am too short of time.

'Mike Woodhouse' on Thu, 14 Sep 2006 07:56:12 GMT, sez:

On my "to-do" list is investigating the Migrate bits of Ruby on Rails, which looks like an interesting approach.

'Jiv' on Thu, 14 Sep 2006 08:47:46 GMT, sez:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/TEDBPro.asp

Yeah i too played around with Data Dude ... last weekend, looks decent but would be a while before it catches up with Red Gate compare.

Its in CTP 5 right now.

'Casey Barton' on Thu, 14 Sep 2006 10:55:30 GMT, sez:

I use an approach like Paul's (building the DB from a set of text files kept in conventional source control) and I'd never go back to a system that wires itself into your database.

I'm using a VBscript db build script that was on MSDN: http://msdn.microsoft.com/msdnmag/issues/04/09/CustomDatabaseInstaller/ I've been customizing it as I need more functionality.

The chief advantage is that the db version control is completely integrated with the rest of your app. The database is just another component in your application, why should it be off in its own repository? Labels and patches then cover all aspects of your app. And in Subversion, when I commit codependent db and code updates, the commit will succeed or fail as a unit and never break the build.

'http://' on Thu, 14 Sep 2006 11:39:12 GMT, sez:

Check out SQL Delta. I swear by it for SQL Server.

'Tom Pester' on Thu, 14 Sep 2006 11:44:03 GMT, sez:

I have used red gate sql compare (and data compare) for a while which are good products.

But sadly they charge a lot of money for updates that they release very often and are not worth it. They abuse their position as market leader atm...

I bough the apex version for this reason and I could upgrade from redgate at a special price. I believe it was 160 dollars or so.

Although apex is not perfect, the people over there provide a support that is unprecedented and they give basicly a life time of upgrades.

So I advice both red gate (which I consider better in terms of usability and slighlty better in quality) and apex sql (they listen and act upon what you say).

Its also important to note that the scheme comparision as well as data comparision is integrated in apex.
If you go for the red gate bundle this will set you back $490 dollars so apex is better value for money.

'Leonardo Herrera' on Thu, 14 Sep 2006 12:50:46 GMT, sez:

We use a cron job that runs a perl script that extracts all the procedures from our Sybase database, checkout a current version from our CVS, copy the extracted procedures over the CVS files, perform a cvs update, and if there are any differences (cvs diff), commit them. Kind of cumbersome but very effective.

'Mike Hadlow' on Thu, 14 Sep 2006 17:37:13 GMT, sez:

Thanks for the mention Secret Geek! Just to fill in a few more details about DbGhost. It was developed by a guy I worked with at NTL (here in the UK), Mark Baekdal. He started the company with a couple of the other guys there, Malcolm Leach and Abdul Baruwa. They're all top notch developers and Malcolm especially has been really helpfull when I've introduced DbGhost into subsequent projects. I'd recommend trying it out if you're looking for that kind of tool and if you're in the UK and looking for large scale project build management consultancy give Malcolm a call, he almost single handedly saved the Harmony project at NTL.

Saying all that, I haven't looked at the team system stuff that Jeff Atwood mentions above. I'll have to investigate.

'http://' on Thu, 14 Sep 2006 20:40:14 GMT, sez:

http://www.sqlmanager.net/products/mssql/dbcomparer

looks good. Does anyone have experience with this?

'Heiko Leuze' on Fri, 15 Sep 2006 12:47:20 GMT, sez:

I'm using the light version of "xSQL Object 2" (compares schema and synchronise it) and "xSQL Data Compare 2" (compares data and synchronise it) from www.x-sql.com.

Until now it always did a good job for me !

'Dale' on Fri, 15 Sep 2006 21:33:58 GMT, sez:

DeZign for Databases - http://www.datanamic.com/

30-day free trial download
Pricing starts at $229

'Chris' on Mon, 18 Sep 2006 23:11:42 GMT, sez:

All i do is in sql 2000 use right click, all tasks, generate script, 1 file per object.

I script this to a DB directory and include it in normal source control (svn).

It works great, will show table changes, index changes, sp changes, etc.

Since migrating a few projects to 2005 this script function is no longer available. It will be returned in the next service pack, but for the moment i have written a custom SMO app which achieves the same purpose.

'Steve Hebert' on Tue, 19 Sep 2006 06:59:09 GMT, sez:

Another option is using a Database Project is Visual Studio.

Schema upgrade scripts must be capable of running multiple times on the same server without erroring out.

I blogged the process of gathering the scripts for redistribution here...

http://codebetter.com/blogs/steve.hebert/archive/2004/11/11/31689.aspx

'John G' on Wed, 20 Sep 2006 02:03:42 GMT, sez:

I have been using the free lite edition of xSQL Object (www.x-sql.com) - so far it's been great. The interface takes a bit getting used to but they have some cool features that have come in handy at times, like scripting schema and data together, taking snapshots of the schema etc. By the way, the "official" tool for our department is Embarcadero's Change Manager but xSQL Object (I compared the full edition) seems to outperform it - hands down!

'Martin Lange' on Mon, 16 Oct 2006 01:14:43 GMT, sez:

I'm another DB Ghost fan. Like Mike Hadlow, I worked at NTL on the Harmony project where I was introduced to DB Ghost. I can't rate it highly enough. Mike's right, DB Ghost saved the Harmony project.

The main point about DB Ghost is that it doesn't just do version control. What makes it unique is that it's a compiler for your database source code. DB Ghost can verify all your database source code BEFORE you check it in.

In 2004 I left NTL and returned to Canberra (Australia) where I'm working as a technical lead for a .NET development team. I introduced DB Ghost here and the team have never looked back. We use it daily. In fact, DB Ghost has been so successful that it's been adopted as a standard development tool for the entire IT department. To learn more about what we did, go to http://www.dbghost.com/compare_sql_case_studies_dest.asp.

Do yourself a favour, get yourself an eval copy of DB Ghost and check it out.

Martin.Lange@DEST.gov.au

'Me' on Thu, 14 Dec 2006 05:54:19 GMT, sez:

What about powerdesigner?

'Max Guernsey, III' on Mon, 14 Jan 2008 18:49:57 GMT, sez:

I use Hexagon Software DataConstructor (which, admittedly, I wrote). With it, I'm able to develop my database using TDD. I use one database create script for my acceptance tests, database unit tests, transition tests (testing getting from one version to another), test servers, and production. Because my the exact database upgrade path I use is tested about a thousand or more times in a sprint and the same for every instance of my databse, I never sweat a database deploy: it's just like rolling out any other part of my application.

'SUMsoft Solutions' on Tue, 15 Jan 2008 06:18:59 GMT, sez:

Schema Version Control for Oracle (SVCO)

It's the integrated version control solution for Oracle database server schema objects! You can track all schema objects changes direct within Oracle database. No extra version control tool is required. You can go on and use your favourite IDE for Oracle or even mix different tools. You can also integrate Schema Version Control for Oracle in your own development process.

'lb' on Tue, 15 Jan 2008 06:22:22 GMT, sez:

"SUMsoft" -- you've read their spam, now try their software

I censored my own comment here... too harsh. Sum soft looks alright

'Max Guernsey, III' on Tue, 15 Jan 2008 22:16:24 GMT, sez:

lb - I know. Seeing as how I just posted a similar comment, I'm inclined to give them a break, though. It's hard to get the word out, man. ...and, if you think you have a product that can help people, it's not altogether ignoble.

-- Max

'lb' on Tue, 15 Jan 2008 23:32:25 GMT, sez:

@max, @SUMsoft

guys i really apologize -- i was way too harsh.

'Max Guernsey, III' on Wed, 16 Jan 2008 13:31:27 GMT, sez:

lb - If someone cannot chuckle at what you posted, which was actually quite funny, then they are not cut out to build a business.

-- Max

'Albert' on Mon, 04 Feb 2008 11:26:02 GMT, sez:

+1 for xSQL Object & xSQL Data Compare from www.xsqlsoftware.com

'Andreas Jansson' on Tue, 05 Feb 2008 10:09:17 GMT, sez:

I can recommend SQL Examiner for comparing databases and generating update scripts. I found it to be the best when I compared a number of similar tools about a year ago, and I use it frequently.
Have a look at it: http://www.sqlaccessories.com/Default.aspx
Costs about $300 for a one-user license.

'michael rosquist' on Wed, 27 Feb 2008 09:43:37 GMT, sez:

Recommends Power Designer (12.5)
Excellent reporting and drawing facilities.
A very good script generating facility with good way to customize db object names.

'gk' on Sat, 01 Mar 2008 08:32:44 GMT, sez:

Anyone knows anything about mySQL Version Control??

'The Daily WTF' on Wed, 28 May 2008 21:21:40 GMT, sez:

Quote From TheDailyWtf (http://thedailywtf.com/Articles/Overdue-Retirement.aspx)


I looked at the SQL Server database they have behind their web product and asked 'How is version control done?' The exact response (not a single word of this is a lie):

"'We upload a version of the database to one of the customer websites and if it works we gradually copy it over to the other customer websites. We don't install it on all of them so that if we need to look at an older database version we know which customer website will have it and we just copy that back.'

'Thomas' on Mon, 28 Jul 2008 10:04:30 GMT, sez:

This looks interesting:

http://code.google.com/p/migratordotnet/

1. Open source
2. Inspired by Rails ActiveRecord
3. Handles migration roll-forward/roll-back

Will trial it over the next few months

'Alexander Karmanov' on Thu, 18 Sep 2008 16:42:11 GMT, sez:

Almost all of those tools address problem of synchronization/existing database update too late.
I created a tool for myself that updates the database from scripts introduced via change log.
Files in my case are organized not by type (SP vs. UDF vs. table) but rather grouped by purpose in config files. Those config files are linked to each other. Thus (1) the script deploying database is quite simple, (2) it is stored in TFS/SourceSafe/Subversion easily.
See article - there is the tool itself and example folder/file structure.

'Tiziano Mengotti' on Thu, 29 Jan 2009 10:48:36 GMT, sez:

hi, if you create your sql scripts by hand and want to manage them, you might be interested in deltasql.

It is a webpage where developers submit all scripts in a centralized manner. On each database schema there is a special table that stores the version of the latest executed script.

The software is open source and can be tested at http://www.gpu-grid.net/deltasql
(if you login as admin with password testdbsync)

To download the tool, point your browser to http://sourceforge.net/projects/deltasql

'itamar' on Fri, 27 Feb 2009 18:20:14 GMT, sez:

Nob Hill's SQL version control.

http://www.nobhillsoft.com/Randolph.aspx

'Rob G' on Sat, 28 Feb 2009 05:27:36 GMT, sez:

We are using dbMaestro TeamWork (Oracle version).
It allows checkin/checkout on DB objects + bunch of all kind of change management actions, like version comparison, rollbacks, merging, deployment scripts generation, code source control tools integration (it's TFS in our case),etc.

Real nice.

http://www.dbmaestro.com/DbMaestro/products/1/6/448.aspx

'Frank' on Fri, 08 May 2009 12:55:00 GMT, sez:

We are using Sync For SQLServer from SleepyAnt Software. It is the basis for the complete db update process. It simply creates an SQL script which changes the schema from one version to the next version. We then create exact version transition packages from e. g. version 1.3 to 1.4. You can also take over data from one version to the other.

'Nathan Rozentals' on Mon, 27 Jul 2009 02:22:19 GMT, sez:

Hi All,
I've used Visual Studio for Database Pros for a few projects, but some of my clients are not able to afford the price tag.
So for SQL Databases, I've written my own tool using SMO objects and simple dos .bat files.
Have a look:
http://dbsourcetools.codeplex.com/
- Nathan

'David Atkinson' on Mon, 19 Oct 2009 07:04:53 GMT, sez:

Red Gate is developing SQL Source Control, a SQL Server Management Studio add-in that links a development database with a source controlled representation, providing object-level committing and retrieval. Although the official release won't be available until 2010 there will be early builds available. If you're interested, register your interest at:
http://www.red-gate.com/products/SQL_Source_Control/index.htm

David Atkinson
Product Manager
Red Gate Software

'MikeK' on Tue, 27 Oct 2009 16:34:44 GMT, sez:

We use a modified DbDeploy java progarm. Allow for incremental changes to database that are aligned with middle tier development. Also allows for a unified deployment process.
see the google project at:
http://code.google.com/p/dbdeploy

'EB' on Mon, 06 Sep 2010 15:09:38 GMT, sez:

Pretty much all of the software packages I've seen are flawed. They claim to version your database, however, all they do is manage scripts, or compare databases.

How would you like to manage your java source code by checking in series of differences between one version of you java code and the next. That's absurd!

Another idea that doesn't add value is using text-based source control tools to manage your db. That just doesn't add any value. You will not use the versioning abilities of the underlying SCS, and the SCS isn't able to update your database in the same manner that is updates your source code, so why would you use it for such a purpose.

We need a true database artifact management solution, that is vendor-independent, that versions each table, view, and so on.

I have been working on something like this, but there's nothing on the market so far.

'RP' on Thu, 09 Sep 2010 17:13:38 GMT, sez:

2 more suggestions:

1. Agile DB Recreation/Import/Reverse/Export tool http://www.thisisagile.com/dbrire
easy CI integration etc.
It's our tool and it's free


2. http://dbcomparer.com/. I didn't try it, just read through the site
It's also free

'RP' on Thu, 09 Sep 2010 17:22:27 GMT, sez:

To "EB". What do you mean as versions for every table, view and so on?
What if your DB change includes several related tables? IMO it must be one atomic version change. We use SVN for DB versions and we use Agile DBRIRE for automatic export DB meta-data and data into SVN. In the result, you can rollback your DB to any version in SVN.

'EB' on Fri, 17 Sep 2010 20:01:32 GMT, sez:

RE: What if your DB change includes several related tables? IMO it must be one atomic version change.


Absolutely correct! A good example would be changing a one-to-many relationship to a many-to-many and migrating the data. Such a change will affect several tables, and should be atomic

Storing DDL in text-based source control tools is useless, since the dbms already does that for you (you can always backup a database and extract ddl from it).

You may argue that by also storing alter statements you are adding value, however, since SVN doesn't retain any intelligent information about sequencing the hundreds of changes you've added no value.

Database rollback: Best practice is to make a backup prior to each upgrade. Storing production data in SVN is just plain !@#$#. How am I going to do that to all 20 of my production sites (500 tables, 1000 programmables), each at a different version, and each containing millions of rows worth about 30GB.

If an upgrade fails, you must restore from backup. I see no other solution. I've noticed that SQL Server allows a transaction DDL to roll back, so you may be able to rollback a script, fix it, and resume. Oracle does not. You cannot rollback DDL. If you started a DDL script, you better have a backup.

'Brian Krebs' on Wed, 06 Oct 2010 21:18:07 GMT, sez:

EB, I completely agree with you. Database Versioning Systems has an innovative new product called DBVS. This software addresses the considerations you outlined in your comment.

DBVS is absolutely vendor- and platform-independent in a number of ways. First, it integrates with several SCM systems including Subversion and MKS. It also supports all of the major RDBMS players including Oracle, SQL Server and DB2.

The way it addresses your concerns is at a fundamental level. DBVS versions XML representations of your database objects rather than SQL scripts. This allows it to calculate deployments on the fly by comparing the revisions of each object you want to deploy with the live objects in the target database.

This comparison then creates a set of changes which are ordered based on dependencies and translated into SQL code based on the target database's platform. In the same way, DBVS can handle automatic rollbacks. Each of these operations is handled in a single transaction so they are completely atomic as you mentioned.

You can find out more at http://www.dbvsys.com. Any comments or feedback is welcome.

Brian Krebs
Chief Technical Officer
Database Versioning Systems

'RP' on Thu, 07 Oct 2010 13:59:50 GMT, sez:

EB, No I'm argue for anything. I'm not agree with your propose. For our personal use we built and use a tool - DBRIRE it represents DBMS into XML structures and it allows to use any versioning system, to have atomic changesets, incremental updates, rollback DB structure and data to any specific point. The tool is free. Everybody can try it. Any feedback will be highly appreciated. Additional details here: http://www.thisisagile.com/dbrire

'Christophe' on Mon, 25 Oct 2010 05:58:47 GMT, sez:

I suggest you have a look at neXtep designer : it is a free GPL database development environment built on eclipse RCP proposing version control of database structure, data model diagrams, synchronization and reverse synchronization features and delivery automation.

The version control allows you to create branches and to merge your developments between those branches.

Have a look at the wiki to read about the concepts / tutorials / overviews :
http://www.nextep-softwares.com/wiki

'Jan' on Mon, 25 Oct 2010 15:26:58 GMT, sez:

Yet another possibility to maintain versions: http://datadmin.com/en/functions/dbversioning
It is GUI tool for Windows, supports MS SQL, MySQL, SQLite, Postgres, Access and EffiProz.

I am using it for versioning large MS SQL database and couple of small SQLite databases. It stores database models in readable XML files (no explicit CSV or SVN support is supported) , can generate C# code for handling DB upgrade.

It is handy with combination with DatAdmin GUI, eg. you can easy do reverse engineering (drag & drop table from DB to model).
Another example of this symbiosis is ability import table static data (content of validation tables) from CSV.

'Jollo' on Fri, 26 Aug 2011 08:30:49 GMT, sez:

Hi all,

I’m looking for 3 simple things... well, I thought of them as simple, but I’m realizing they’re not to be taken for granted!

1) a reverse engineering tool that I can point to an Oracle schema and get a “baseline” script to re-create that schema from scratch, with decently formatted DDL files (1 per object) neatly organized in a directory tree (by object type) and called in the correct order. Icing on the cake would be an option to pass the tool a list of tables containing static data and get DMLs to populate (insert) those tables as part of the script.

2) a diff tool that I can point to a pair of Oracle instances (source and target) containing a given schema and get a “delta” script to alter the target schema to become identical to the source schema. If data loss occurs on the target instance (i.e. drop a column) I would like to find a warning comment inserted in the script (e.g. “-- Attention: data migration DML needed here?”). Icing on the cake would be an option to pass the tool a list of tables containing static data and get DMLs to update (delete, update and insert) the data in the destination tables to become identical to the contents in the source tables *without* deleting and re-inserting all rows (or dropping and repopulating the table).

3) I would like the above two tools (that, as you will have recognized, are basic to putting your database design under version control) to be open-source, with a command-line interface and a vibrant community backing them.

I must be one out of a couple million people asking for the same things over and over again: I’ve seen the questions all over the internet but I could find no straight answer. Please help!

Thanks and take care.

name


website (optional)


enter the word:
 

comment (HTML not allowed)

All viewpoints welcome. But the right to delete any post for any reason is reserved. Don't make me do it. Aim for constructiveness. Comments may be republished, emailed to your loved ones or printed and used as toilet paper. Also, I get particularly nasty on comment spam. It's not worth even trying to post comment spam here -- your html is escaped, and your links are given a rel='nofollow'. By attempting to post a comment, you understand that if the comment is considered spam, at my absolute discretion, your IP address may be used as the target of a prolonged distributed denial of service attack. Your electricity might suddenly stop working. Your car tyres will go mysteriously flat. You will suffer permanent hairloss. Your dreams will be filled with terrifying monsters. And in any case I reserve the right to record and publish your IP address.

 

TimeSnapper_{Know thyself!}

TimeSnapper is a life analysis system that stores and plays-back your computer use. It makes timesheet recording a breeze, helps you recover lost work and shows you how to sharpen your act.

 

NimbleText is a Powerful FREE Tool

Use it for:

• extracting data from text
• manipulating text
• generating code

It makes you look awesome. Use it right now! Go on! Hurry! Don't walk, run!