How do you deal with source code management and automatic deployment (configuration management) of database tables. I work in a SQL Server environment, and it’s quite easy for the script to delete and create files for stored procedures / triggers / functions, even jobs. It is also easy to handle creating a new db table. However, if you later want to change this table, you cannot simply abandon it and recreate it with a new field, for fear of losing data. Is there an automated way to solve this problem? Did you script exit the temp table and backfill after updating a new modified table? (can be rude if there is a lot of data)
Any suggestions are welcome.
You can automatically create the initial creation of the script, but ALTER scripts really need to be manually encoded in each case, because in practice you need to make custom elements in them.
In any case, you will need some way to create application and rollback scripts for each change and have an installer script that launches them (and a rollback that rolls them back). Perhaps such an installer should remember which version the schema is in and complete all the necessary migrations in the correct order.
See my article here:
http://marksverbiage.blogspot.com/2008/07/versioning-your-schema.html
, Red-gate SQL Compare, , script. , , . , script .., . , alter drop create, .
, procs , . dbas , Source COntrol, .
, , , QA , , script ( - ) , prod. prod, , , , , , .
Oracle DB. SVN ( SVN) . . http://www.scmsupport.com/scm.php?go=home.html http://scmsupport.wordpress.com/ .
, , , SQL DDL.
, Embarcadero Change Manager , Embarcardero.
, , Management Studio, script .
, , ALTER .
Oh forgot to say, make sure you have a good set of database backups before uploading schema changes to production. Better than sorry.