Script schema compare changes between two SSDT projects

Question

I would like to script the changes produced by a schema compare between two SSDT projects, but the "generate scripts" button is grayed out / disabled when the source and target are both SSDT projects.

The "update" button is not grayed. If I want to directly update, I can. But I need the scripts because I'm using this two manage two database environments that are not on the same network (but need to be kept in sync with db schema and code promotions).

Also, just to verify and reinforce, if my target is a real database and not a project, I can generate scripts. Its only when the target is a project that I cannot generate scripts. Unfortunately I cannot directly connect to the target DB.

I would really like to avoid having a database copy of each of my clients DB environments locally just for the sake of generating scripts (I already have SSDT projects for each).

Is this an intentional limitation? Seems odd. I can't be the first guy that has to manage a clients schema that I can't directly connect to from my dev machine.

Answer 1

According to Microsoft's page on Schema Compare (emphasis added):

"You can update the target directly from the Schema Compare window if the target is a database or a project, or you can generate an update script if the target is a database or a database file."

The behaviour you describe appears to be how the functionality is intended to work.

However, if you can build/compile your projects this will produce .dacpac files (aka database file), which you can compare and generate a script, either using the Schema Comparison, or it appears the command line version of SSDT (sqlpackage.exe) appears to support a Source and Target of .dacpac.

Hope that helps.