One-time Upgrades using Visual Studio and Database Projects

By October 24, 2016Visual Studio

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.

2016-10-22-21_17_26-microsoft-visual-studio-administ

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.

2016-10-22-21_20_27-cdatabases-microsoft-visual-studio-administ

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.

2016-10-22-21_24_13-c-microsoft-visual-studio-administ

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!

2016-10-22-21_26_29-cdatabases-microsoft-visual-studio-administ

Did you learn something? Check out some of the other helpful blogs for related topics at blog.falafel.com!

The following two tabs change content below.

Latest posts by Guest Posts (see all)