Can a VS2010 Database Project create re-runnable deployment scripts?

Question

I’m using VS2010 Database Projects as a way of versioning our database code between release versions. So far, the solution provided seems good; however there is one problem I wonder if anyone else has run into.

The upgrade scripts generated from the schema compare view are not re-runnable. As a standard, I normally ensure that all my database release scripts are re-runnable, just in case there should be any issues deploying the release script to a server.

Does anyone know if there is a way to configure VS2010 to create re-runnable upgrade scripts i.e. drop sprocs only if they exist etc.?

Answer 1

Might check out Sql Packager from RedGate

Answer 2

Once you've done the schema compare and synced your database project model with your database you can build it to get the .dbschema file for your database.

This can then be used with vsdbcmd to create a change script to upgrade an earlier version of the DB to match the version defined in the .dbschema file. This will provide you with your re-runnable upgrade script, plus if it fails it will tell you why it failed and no changes to your target DB will be made. It will, however, only be reuseable when going from the same database version to the same database version.

One of the main reasons we moved to database projects (from redgate sql compare generated scripts) was to get away from this problem. All our upgrade scripts were from a specific version to a specific version. One of the main benefits of DB projects is that you don't need to have re-runnable scripts. You have the .dbschema file which defines how the target should end up, and you let VSDBCMD do all the work. We don't tend to even generate the scripts, we use VSDBCMD to update the database directly.

If you go down this route http://blogs.msdn.com/b/bahill/archive/2009/02/21/deploying-your-database-project-without-vstsdb-installed.aspx has a list of pre-reqs needed to run VSDBCMD outside of your dev environment.

Answer 3

The drawback of vsdbcmd is that you'll have to jump over your head in order to add non-NULLable columns to a table. Especially if you have a lot of data there.

Answer 4

In Visual Studio 2015 (and possibly earlier versions) you can set the publish script to include a single transaction with error handling. See below:

SSDT publishing with "Include Transactional Scripts"

Wrapping the normal script in a larger transaction wouldn't work for me because the script introduces sqlcmd error handling outside of the pre-/post-deployment scripts that are available within the Visual Studio project:

:on error exit

This stops you using try-catches to handle errors effectively and leaves you having to rollback the transaction manually

Note: the pre-/post-deployment scripts run outside the transaction generated by the "Include Transactional Scripts" setting, so you will have to manage these yourself