Every now and then there comes a time when you release a new version of you application. Some of the new versions do not require changes to the database schema, but there are times when such a change needs to be done.
So imagine that we have an application that several hundred of our customers run. The best possible situation is that all of them have the same version of a database schema. Then we do not have to test all the possible upgrade scenarios. Mostly however there will be multiple versions of databases that are out there and you have to handle all of them.
But what is really the problem you ask? We have developers that designed the database at some point in time, created an application that was sold with that particular schema version. Then, upgrades were made, between that version and the version that we are about to release now and developers have been told to create SQL upgrade scripts so that the we can ship them with a new version. All fine if developers were doing those upgrade scripts while changing the database. Worse if you have to make a diff of the initial database schema and the desired one. Either way it was, is and always will be prone to human error. People are error prone. We, developers make mistakes, especially when doing some mindless (most of the time) activities such as comparing databases.
So, how sure you are that the database upgrade scripts that you are about to ship are the right ones? Hard question. One thing you could do is to take an initial database, run the scripts and compare the schemas. I will post a short tip on how I do it with Microsoft SQL Server 2000 Enterprise Manager. If there are errors, correct them, run the scripts again, repeat until there are no differences (or you see none).
Another problem is that the schema is not the only thing that needs to be shipped. Imagine that there is some fixed data in your database that is constant. Imagine that this data need to be altered. Same solution exists as mentioned above. Do it by hand either at the end or incrementally.
Is this solution perfect? Is it cheap? Is it time and cost effective? When you think about it you will find out that there should be some automation available. And rightfully so. There are tools that let you compare the databases, point out the differences and even create an SQL upgrade script for you. Keeping in mind what was said earlier it should be obvious that such a tool is a must have tool in every software development company that does sometime upgrade its databases.
As mentioned, there are tools that help you automate this process. I have recently found the SQL Delta application that lets you do eliminate this time consuming and error prone process of manualy generating upgrade scripts. Now if I had such a tool back when I was wasting my time making the scripts instead of solving the real business problems...