[Pxtl]

Microsoft has a product called SSDT for this. You provide the SQL schema, it compiles a file called a DACPAC, and provides tools for deploying a DACPAC against a database. Where it can figure out the migration (eg. changing column types, adding columns) it does so automatically. Where it can't (dangerous changes, dropping columns) it blocks.

The challenge is that more complicated refactoring like renaming columns has to be done through a weird xml DSL.

It also treats static/initial data as out-of-scope.

And it's a product of the bad old days of clumsy closed-source designers and the like.

I use it extensively... and in general, wish I didn't. Maybe it's "grass is always greener" but I wish I'd just stuck to a stack of migration scripts. The fact is that 99% of the time you're deploying to an existing SQL server, so expressing your schema as a series of sequential changes is the only workflow that makes sense, sadly.

[WorldMaker]

Data migration (including static/initial data) can in theory be done using BACPAC (and DACPAC and BACPAC share a file structure so you could start with an SSDT DACPAC and expand it, in theory), but the tools that work with BACPAC in general are even flakier and BACPAC was primarily designed for backups rather than data compare, which is why SSDT mostly opted out of managing BACPACs at all and stuck primarily to the DACPAC subset when outputting artifacts (and part of why DACPAC and BACPAC even have different names). (SSDT can read BACPACs pretty well as I recall, and using BACPACs in SSDT for things is still sometimes useful.)

(Plus, yeah, BACPAC doesn't solve all the other problems with DACPAC that most of the tools are still clumsy mostly closed-source things that work with Microsoft's SQL Server alone.)