How can I set my deployment options to script the incremental release of a Visual Studio 2010 database project?

Question

I've just started using a VS2010 database project to manage the release of an update to an existing database.

I want the deployment option to generate a script that will contain the commands to change my existing database rather than create an entirely new one.

E.g I have 10 existing tables - one of which I drop in the new version and I create some new sprocs. I only want the deploy to script the Drop table and Create Procedure commands.

I am using VS2010 Premium.

Is there a recommended standard approach I could follow to managing DBs in a project from initial creation to incremental releases?

Thanks!

Answer 1

I asked a similar question a while back on the MSDN Forums and was told that the recommended way to do this is to use VSDBCMD. Basically, you output a schema file from your database project which contains all of the information about your database, and then you run VSDBCMD to compare your schema to the target database. This in turn creates the script to update the target database to your current schema.

The rationale for this approach is that just because you and I may think we know what the target database's schema looks like we can't really be sure until we let VSDBCMD run the comparison. Who knows, someone else may have modified the schema in the target database without our knowledge, so our change script may end up failing for some unknown reason.

I really wasn't terribly satisfied with this approach and ended up continuing to use my "old approach" of hand-coding my change scripts when necessary, but I am eager to see if anything has changed in 2010 that makes this a bit easier to work with. I'd really like to see a simple API that does what VSDBCMD does so I can put a GUI together to simplify updating a target (in my case, client) database without the person running the upgrade having to be a DBA.

Answer 2

There is an "Always re-create database" in the project's .sqldeployment file. Unchecking this option will result in an auto-generated SQL script that will incrementally update your database without dropping it first.

There is also an option to "Generate DROP statements for objects that are in the target databse but that are not in the database project." You will need to check this option, if you want tables, stored procs, etc. to get dropped if you've deleted them in the database project. This will delete any table, etc. that users may have created on their own for testing, debugging, etc.

To change the options in the .sqldeployment file. Open the file in Visual Studio. Either expand the database project in the solution explorer, the double click on the .sqldeployment file (it will probably be in the Properties folder under the DB project). Or open the properties page for the database project and click the "Edit..." button next to the "Deployment configuration file". Check or uncheck the options you want when the database deploys.

I use VSDBCMD.exe for 1-click build & deploy scripts I've created. It works very well. VSDBCMD uses a .sqldeployment file -- the default .sqldeployment file is specified in the .deploymanifest file, but it can be overridden by specifying it as a parameter when executing VSDBCMD. Also, I believe that Visual Studio uses VSDBCMD under the covers when it deploys the database project, but I just assume that to be the case since the functionality is pretty much identical.