This is what we do, but we are PHP developers not VS so maybe Microsoft has a better approach, but the idea should be the same.

We keep a change log table in the database. Each time the update scripts execute this table is updated to reflect the current state of the database. It also keeps a history of changes (this allows you to spot customer database’s that might of had special hotfixes installed that others don’t have).

CREATE TABLE `changelogs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `version` varchar(45) NOT NULL,
  `previous` varchar(45) NOT NULL,
  `log` text,
  `started` datetime DEFAULT NULL,
  `finished` datetime DEFAULT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `created` (`created`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1$$

There are two types of updates from source control that the developers will need to know about.

1. The update is redundant, meaning they can simply delete a record from their database change logs and re-run the updates. The updates don’t require a critical point in the schema.

2. The update is dependant, and they must rollback the database to a stable version so the updates can bring it forward. The updates require a specific schema.

While customers may be running version 2.0.0 of the database. Developers may only have to rollback to 2.9.9 to test upgrading to 3.0.0 (beta). You can also have downgrade scripts that perform quick hacks to the database to make the upgrades safe to run.

The update process is basically any automated technique of your choice. We use a directory in each project that stores a list of SQL files. The directory contents look like this.

install.sql
update.txt
update-2.0.0.sql
update-2.0.1.sql
update-3.0.0.sql
hotfix-3.0.1.sql

install.sql is our zero point entry script. It creates the database for new installs.

update.txt is our update guide for the deployment script, and the rest are SQL files to be executed.

The contents of the update.txt look like this.

#
# Defines the update mapping of which SQL files are executed.
# The order of this file isn't important, but do not create
# endless circular versions (i.e. update 3 to 4, then 4 down to 3).
#
# Usage for each line:
# [current_version]=update-[new_version].sql
#
3.3.14=update-3.4.0.sql

# DATE: 20130107 144921
3.4.0=update-3.4.1.sql

The only value the deployment script understands is the first current_verion part. That equals a file. When the deployment script executes it reads what the current version is from the changelogs table, and then executes the SQL file that matches that version. This steps the database forward in the schema changes. This process repeats until there are no more matches in the update.txt file (order in the file isn’t important).

This approach works fine for us. We can single out customers with hotfixes, but still migrate then to formal releases later. Even install small SQL change files to bring them up to date from their hotfit.

The only problem with this approach is that it requires discipline by the developer. We also separate this process by software module, rather than one large update directory per application. For each module we prefix the changelog table. So we have multiple tables called things like documents_changelog, and users_changelog. That has worked well for us since modules are shared across multiple applications.

My solution is to have a schema version table in the database.

If the (max) version number defined in the scripts (I actually do this in code: see https://softwareengineering.stackexchange.com/a/142553/866 and variously on stackoverflow) doesn’t match the max number in the database then you have a problem (my apps simply refuse to run if there’s a mismatch – but you could be more flexible than that).

This is a simple (crude even!) solution but it works well for me. On a legacy system we’ve got even cruder – we have a table of applied scripts (from the point at which we got slightly smarter) with the scripts prefixed yyyy-MM-dd so they sort. Shortly we’ll have another script to see which haven’t been applied.

The key to me is being able to compare an actual schema version/list of scripts applied with an expected version/list of scripts.

There are now newer toys to do this sort of thing – EF Migrations seems to be getting close to the point where it will work wonders – but for now I have a pattern that works well for me.

What you are looking to do is do database migrations. There are a number of different tools out there that handle this in a number of ways. Given you already have a bunch of scripts that should be runned in order, I would start with RoundHouse as it can handle the legacy stuff you’ve got as it is just a sql script runner on serious steroids.

One protip for integrating it with the existing db — the trick is to migrate a new “database” and then grab the roundhouse tables and insert them into production. Once you start shipping backups out to dev again it will have a copy of the roundhouse stuff so the migrations work. Moreover, you can also apply the migrations on production directly at that point and have all the benefits of the tool.