Rather than trying Opensource or free tools, I would highly recommend using Redgate tools for schema comparison. (note: I am not working for or affiliated to Redgate, but have and is using the schema and data compare and trust me they are life saving !)
For sql server : SQL Compare.
Useful resource for automating using Powershell or command line.
for Oracle : Schema Compare for Oracle
Note: Just to mention, there is Data compare to sync data as well.
I did something very similar, but not as a single script. I used migrations. I also described this in Version Control and your Database. And indeed, every step is a different script, and the application drives the upgrade (the migration). Every step is tested. Every change is an upgrade, be it DDL or DML, it matters not. There are DML changes like changing some catalog entries or application lookup
tables, these are upgrades. Of course, I'm not talking about DML for the actual application content.
Mixing DML and DDL should not cause problems. Scripts can contain multiple batches. Mixing DML and DDL inside a transaction can lead to problem though, and should be avoided.
Do not try to make the scripts idempotent (safe to run twice). That is going to add a tonne of problems like check if table exists etc. Do not try to make the scripts ACID (either all upgrade succeeds, or nothing); it is impossible. In case of error, revert to a backup. If the cost of restore is prohibitive (huge DB) then you should have a good battery of tests.
Test the scripts with significant size DBs so you don't run into size-of-data operation surprises in production upgrade.
Oh, and if these erorrlog messages look familiar:
Converting database 'x' from version 611 to the current version 655.
Database 'x' running the upgrade step from version 611 to version 621.
Database 'x' running the upgrade step from version 621 to version 622.
Database 'x' running the upgrade step from version 622 to version 625.
that is because this is how the SQL Server itself takes care of schema versioning.
Best Answer
You can create a new filegroup, make it
DEFUALT, create all schema objects and switchDEFAULTfilegroup back. If at any later point you would need to add objects to that schema, you can explicitly specify filegroup throughON filegroupclause for each object in create statement or temporarily switchDEFUALTfilegroup.Alternatively you can explicitly specify
ON filegroupfor each object of this client's schema.