I frequently encounter the next problem.

Sometimes on some changes to some project that need new tables or posts within the database. I result in the database modifications and continue my work. Usually, I recall to create lower the alterations to ensure that they may be duplicated around the live system. However, I do not remember what I have transformed and that i don't remember to create it lower.

So, I create a push towards the live system and obtain a large, apparent error that there's no NewColumnX. Ugh.

No matter the truth that it isn't really the very best practice with this situation, it is possible to version control system for databases? I do not worry about the particular database technology. Among the finest to understand if a person is available. If it will happen use MS SQL Server, then great.

In Ruby on Rails, there's a perception of a migration -- a fast script to alter the database.

You generate a migration file, that has rules to improve the db version (for example adding a column) and rules to downgrade the version (for example getting rid of a column). Each migration is designated, along with a table monitors your present db version.

To _migrate up_, you take an order known as "db:migrate" which examines your version and is applicable the appropriate scripts. You are able to migrate lower similarly.

The migration scripts are stored inside a version control system -- if you alter the database you sign in a brand new script, and then any developer can use it to create their local db towards the latest version.

I am a bit old-school, for the reason that I personally use source files for creating the database. You will find really 2 files - project-database.sql and project-updates.sql - the very first for that schema and persistant data, and also the second for modifications. Obviously, both of them are under source control.

Once the database changes, When i first update the primary schema in project-database.sql, then copy the appropriate info towards the project-updates.sql, for example ALTER TABLE claims. I'm able to then apply the updates towards the development database, test, iterate until done well. Then, sign in files, test again, and affect production.

Also, It's my job to possess a table within the db - Config - for example:

SQL

CREATE TABLE Config

(

    cfg_tag VARCHAR(50),

    cfg_value VARCHAR(100)

)

Place INTO Config(cfg_tag, cfg_value) VALUES

( 'db_version', '$Revision: $'),

( 'db_revision', '$Revision: $')

Then, I add the next towards the update section:

UPDATE Config SET cfg_value='$Revision: $' WHERE cfg_tag='db_revision'

The db_version only will get transformed once the database is recreated, and also the db_revision provides me with a sign what lengths the db is from the baseline.

I possibly could keep your updates in their own individual separate files, however i made a decision to mash them altogether and employ cut&paste to extract relevant sections. A little more housekeeping is needed, i.e., remove ':' from $Revision 1.1 $ to freeze them.

Most database engines should support dumping your database right into a file. I understand MySQL does, anyway. This can you need to be a text file, which means you could submit that to Subversion, or anything you use. It would be simple to operate a diff around the files too.

If you are using SQL Server it might be unequalled Data Dude (also known as the Database Edition of Visual Studio). When you get used to it, carrying out a schema compare involving the source controlled version from the database and also the version in production is easy. With a click you will get your diff DDL.

There's an training video on MSDN that's very useful.

I understand about DBMS_METADATA and Toad, but when someone could develop an information Dude for Oracle then existence could be really sweet.

Have your initial create table claims in version controller, adding alter table claims, but never edit files, just more alter files ideally named sequentially, or even while a "change set", to help you find all the new changes for the deployment.

The hardiest part will be able to see, is monitoring dependencies, eg, for the deployment table B should be up-to-date before table A.