What's the easiest method to maintain upgrade scripts between versions of the product? If your customer begins with version 3 of the product and would go to version 5, what's the easiest method to generate upgrading script for that customer to ensure that any variations within the database schema between versions 3 and 5 are resolved?

1) Make use of a Tool like RedGate's Schema and Data Compare

2) Use Visual Studio 2008's GDR project

3) Write your personal!

It has been talked about many occasions 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 appears to become the most well-liked approach in individuals questions. I actually do like this approach, but when your situation is straightforward you may would rather keep SQL to do the alterations inside a table using the version number, such as this:

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

after which you can aquire a script to do the upgrade having a simple:

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

(adapt to taste, obviously).

This can not work if you fail to do all upgrades via SQL, by which situation I suggest migrator.internet

Try Wizardby to ascertain if they fit. It features a special DSL for writing migrations, it may instantly generate downgrade scripts also it tracks versions, so it's not necessary to write any code to achieve that.

If you wish to do upgrades programmatically, see this.

It completely is dependent in your database structure and also the additional features the brand new version has. When the latest version is climax that old one you might like to come with an ETL process to complete the upgrade rather than just SQL scripts

Versioning can be treated by using a strategy much like what dbdeploy or ruby rake migration follow. You sequentially number each update script and also have a table within the database that stores all of the script amounts that happen to be implemented.

The upgrade tool simply is applicable the scripts with amounts greater than individuals already implemented around the database.

Find out more here.