I have had the pleasure of using a Visual Studio Database Project on a complex project lately, which was an upgrade after several iterations of manually maintained database versioning. Actually upgrade is probably a huge understatement. The ability to use reasonable source control practices and synchronize changes for multiple developers has been invaluable. For everyday development it has been a major improvement.
But not everything is everyday development, am I right? Sometimes a task is a one-time operation – maybe you need to bridge a gap or solve an uncommon problem. I got a request from someone working with a much older, separate-branch, version of a database which they wanted to upgrade to be compatible with a specific feature. I was familiar with generating schema comparisons and the resulting custom upgrade scripts, but only for connected databases or project files.
What’s great is that you can compare schemas and get upgrade scripts just using a DACPAC, or Data-tier Application Component Package file all by itself. No connection to the actual live database needed! The dacpac file is generated on a build, or you can also create one using the snapshot option from inside Visual Studio.
Compare using a Snapshot
Creating the snapshots this way will also add a Snapshots folder to your database project for you. This is pretty useful if you want to retain an interim version of your schema. Snapshot is a nice way to think about this dacpac file, it really is a stand-alone representation of your database schema.
Then, in a Schema Compare operation, choose the resulting dacpac file as your target. Finally, generate a script instead of updating a live database target.
Notice that the option to Update Live will be disabled, but generate script will still be active. Then, you can save the resulting .sql file to take wherever you need it!