DTM Schema Comparer on September 13, 2006 23:44 sez:

it's not bad.

Dan F on September 14, 2006 00:16 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 September 14, 2006 00:29 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 September 14, 2006 01:05 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.

Mike Woodhouse on September 14, 2006 03:56 sez:

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

Jiv on September 14, 2006 04:47 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 September 14, 2006 06:55 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 September 14, 2006 07:39 sez:

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

Tom Pester on September 14, 2006 07:44 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.

Mike Hadlow on September 14, 2006 13:37 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 September 14, 2006 16:40 sez:

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

looks good. Does anyone have experience with this?

Heiko Leuze on September 15, 2006 08:47 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 September 15, 2006 17:33 sez:

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

30-day free trial download
Pricing starts at $229

Chris on September 18, 2006 19:11 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 September 19, 2006 02:59 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 September 19, 2006 22:03 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 October 15, 2006 21:14 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 December 14, 2006 00:54 sez:

What about powerdesigner?

Max Guernsey, III on January 14, 2008 13:49 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 January 15, 2008 01:18 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.

Max Guernsey, III on January 15, 2008 17:16 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

Max Guernsey, III on January 16, 2008 08:31 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

Andreas Jansson on February 05, 2008 05:09 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 February 27, 2008 04:43 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 March 01, 2008 03:32 sez:

Anyone knows anything about mySQL Version Control??

The Daily WTF on May 28, 2008 17:21 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 July 28, 2008 06:04 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 September 18, 2008 12:42 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 January 29, 2009 05:48 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 February 27, 2009 13:20 sez:

Nob Hill's SQL version control.

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

Rob G on February 28, 2009 00:27 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 May 08, 2009 08:55 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 July 26, 2009 22:22 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 October 19, 2009 03:04 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 October 27, 2009 12:34 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 September 06, 2010 11:09 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 September 09, 2010 13:13 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 September 09, 2010 13:22 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 September 17, 2010 16:01 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 October 06, 2010 17:18 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 October 07, 2010 09:59 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 October 25, 2010 01:58 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 October 25, 2010 11:26 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 August 26, 2011 04:30 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.

Brenda Zeitlin on March 04, 2012 03:01 sez:

Version control is actually database deployment management - you are not managing the change.
dbMaestro, is a complete database change management solution
that covers all of the aspects of your database from development to deployment
We offer it for FREE for small teams.

Covers all aspects such as:
Control- Control and manage all changes being implemented in the database- no out of process updates or changes can occur to the DB
o Database change repository covering all DB object types
o An enforced check in/out process
o Table structure, PL/SQL code, Meta Data (relevant table contact – usually the data that influences application behavior)
o Saving Object dependencies for future deployments
o Enabling easy rollbacks
o Interconnected to your existing SCM
• Change Documentation
o No code overrides (two developers start working on one package only to lose one's work at some point)
o No undocumented changes are allowed

Deploy – Eliminate risks threatening your deployment and decrease Your Deployment Costs by 95% with an integrated deployment engine
o Create deployment scripts, instead of writing them manually – efficient, reliable, repeatable and documented and a huge time saver
o Three way analysis/ impact analysis
o Merge engine to automatically merge code from different teams doing development on the same time
o Deploy changes based on business requirements (work items, tasks etc)

Protect - Enhance your Database Security – control who is doing what in the database
Track each database change back to the requirement and tasks that initiated it (+ integration with SCM for A to Z coverage)

Comply- Improve Compliance to Regulatory Bodies
o Roles and responsibilities enforcement – (important for audit compliance)
o Instead of determining who can do what based on their access rights (can be very problematic if whole groups are using the same login credentials)
o Make sure each person can do only what he should (linking with directory groups to determine project scopes of work)
o Set roles: a DBA can change table structure while the developer can only change the procedures even if sharing the same login credentials


www.dbmaestro.com

Saeed Nemati on March 07, 2012 07:17 sez:

All of the solutions you've mentioned here are not free. Do we have a free solution for this matter at all?

vter on October 17, 2012 12:07 sez:

I arrived here from K. Scott Allen's blog post "Three Rules for Database Work".

Since he also recommends to version-control the database "as a set of one or more files full of SQL DDL commands", why do we need any of the specialized and not-free tools listed above?

Why not simply use Git?

Fernando Luna on November 13, 2013 16:17 sez:

Not sure why people who are asking about "reverse engineering" a database don't simply look to Oracle Documentation. The package DBMS_METADATA gives you everything you need. No need to install a perl script or any third-party product. Read on at http://docs.oracle.com/cd/E11882_01/server.112/e22490/metadata_api.htm#SUTIL3632 or here's a quick example to get you going...

SELECT DBMS_METADATA.GET_DDL(REPLACE(object_type, ' ', '_'), object_name, owner)
FROM all_objects

As to versioning everything as one file, that's just an opinion. There are plenty of reasons to version individual objects as individual files as well. As to why not 'git'? What's wrong with subversion?

Lastly, I challenge the assertion that most database developers don't use version control. Indeed, I've actually found that most developers that code with java or C# or the OO language du jour tend to not have a great understanding of databases.

Pete Townsend on September 17, 2014 11:35 sez:

DATASTAR is a development tool for managing database components including data through the development lifecycle. DATASTAR aims to componentize data such that it encapsulates a business function. DATASTAR’s unique selling point is its ability to allow organizations to apply their standard source control methodology to data. http://www.data-star.co.uk

Raja on June 04, 2015 02:42 sez:

Where is DBMaestro? It is really a good one.

Cheers,
Please visit me at
www.bigrideoncloud.com

Ronald J on September 05, 2015 20:12 sez:

There's a new distributed version control tool for databases - Klonio (http://klonio.com) - and its modelled similar to Git. They also have a cloud service coming soon, so its like Git and GitHub for databases.

Ricardo on January 28, 2016 14:36 sez:

Free tool for SQL Server: http://servantt.com

Servantt is the WinMerge for comparing SQL Server Databases to Version-Controlled Scripts.
It allows you to:

1) Reverse-engineer your Database objects into a standard and logical structure in file system
2) Compare Database objects to the Scripts folder
3) Differences can be either applied to the Database (updating objects) or to the Scripts.
4) In a single click you can launch WinMerge and easily compare the Scripts to the Database

It supports and encourages best-practices in software development:
1) Keeping Database objects under version-control
2) Removing access rights from developers on production environments
3) DBA review of changes in procedures/views for performance bottlenecks and naming standards
4) Naming objects using fully qualified identifiers and bracketed delimiters

Andreas Kleffel on February 07, 2016 13:06 sez:

Well, one more?

SQL Scripter

It can export the database objects to single files for full version history and create update scripts. Not much, but a lot simpler than most tools referenced here.
It's SQL Server only.

Michael Van Devender on February 17, 2016 11:51 sez:

Here's a free SQL Server Management Studio add-in: https://www.versionsql.com

VersionSQL adds a "Commit" command to the right-click menu in Management Studio's Object Explorer. Right-click a database and click commit to check in the entire database and all its objects. Or right-click a single stored procedure / view / table / etc. and click commit to check in just that schema object.

No separate program to run, no complicated features, doesn't modify your database. Just a quick way to get your SQL into source control.

Uday Shet on June 19, 2017 07:15 sez:

Any solution available for version control mainly for static master data ?.

Jordan on September 25, 2017 06:06 sez:

I can also add dbForge Studio for MySQL (https://www.devart.com/dbforge/mysql/studio/), Oracle and SQL Server to this list.

David on June 19, 2018 08:54 sez:

We use dbForge Schema Compare: https://www.devart.com/dbforge/sql/schemacompare/ . Pretty good so far.

Dave Boltman on February 26, 2019 06:32 sez:

I've used SchemaZen, a command line tool to script out all objects from a SQL Server database into a scripts folder. v1.1.6 seemed to be more tolerant of certain errors in my massive database schema.
https://github.com/sethreno/schemazen/releases