Generating SQL upgrade scripts for the customer

Question

What is the best way to maintain upgrade scripts between versions of a product? If a customer starts with version 3 of your product and goes to version 5, what is the best way to generate an upgrade script for the customer so that any differences in the database schema between versions 3 and 5 are resolved?

Answer 1

1) Use a Tool like RedGate's Schema and Data Compare

2) Use Visual Studio 2008's GDR project

3) Write your own!

Answer 2

I think it completely depends on your database structure and the new features the new version has. If the new version is very different from the old one you might want to have an ETL process to do the upgrade instead of just SQL scripts

Answer 3

Versioning can be handled by following an approach similar to what dbdeploy or ruby rake migration follow. You sequentially number each update script and have a table in the database that stores all the script numbers that have already been implemented.

The upgrade tool simply applies the scripts with numbers higher than those already implemented on the database.

Read more here.

Answer 4

This has been discussed many times before:

http://stackoverflow.com/questions/812210/how-to-automatically-upgrade-deployed-database-for-end-users

http://stackoverflow.com/questions/504909/sql-deployment-strategies

http://stackoverflow.com/questions/466689/any-sql-server-2008-database-change-management-migrations-tools-available/466756

Migrator.net seems to be the preferred approach in those questions. I do like that approach, but if your case is simple you might prefer to store the SQL to perform the changes in a table with the version number, like this:

create table upgradetable (major int, minor int, revision int, change text)

and then you can get a script to perform the upgrade with a simple:

 select change from upgradetable where major > (select major from versiontable)

(adjust to taste, of course).

This will not work if you cannot do all upgrades via SQL, in which case I recommend migrator.net

Answer 5

Try Wizardby to see if it fits. It has a special DSL for writing migrations, it can automatically generate downgrade scripts and it tracks versions, so you don't have to write any code to do that.

If you want to do upgrades programmatically, see this.

Answer 6

Hey mate,
Actually, it's quite easy.
Similar to branching on standard code repositories, what you are looking for is a source control strategy that allows you to do the following:
a. Create a branch of your database structure at a specific point. (v3 / v4).
b. Use that branch to restore a database to a specific version. (i.e. v3 or v4).
c. Create a set of patch scripts to migrate from v3 to v4, or v3 to v5.
d. Test these patches before shipping to your customer.

In this way, you are able to support multiple versions of your database, as well as providing a tested upgrade path.

Have a look at DBSourceTools (http://dbsourcetools.codeplex.com), and particularly the patching strategy.
This tool will allow you to baseline your database at a specific point, and create a named version (v1).
Then, create a deployment target - and increment the named version to v2.
Finally, add patch scripts to the Patches directory for any changes to schema or data.

What this gives you is a repeatable process to test all patches to be applied from v1 to v2.
DBSourceTools also has functionality to help you create these scripts, i.e. schema compare or script data tools.

Once you are done, simply send all of the files in the patches directory to your client.

Have fun.