Currently my database is not under some kind of vcs, we can get deltas but that's about it. I would like to try and make product deployments more automated, and less time consuming. I understand that placing a db's schema files under source control allow you to manage versions, and that these files are basically for dropping the old tables/indexes/etc and then adding the new versions.
My question is, what about the data that's already there? By dropping everything we'd lose all of the data. So, we would have to do a data dump before updating the database, and then re-load the data back after the update has been done. Problem is, some of our largest databases have 80+ GB of data, and we probably have a total of 20 sets of databases (6 DBs per set).
I'm sure that this would work, but given the size of everything, is there a simpler solution that would cut out the need to dump and reload everything each time a schema update took place? And, if not, wouldn't we have to dump the data such that reloading it took in to account the new schema?