Visual Studio Database Project: Include 'If Exists' checks for all the objects

Question

We use TFS Continuous Integration to handle our staging and deployments of code. In our current environment, we (developers) aren't allowed to manually update databases in Production. A script must be staged and then given to a DBA to run.

By default, the database project builds and outputs a database creation script that will create all the tables and stored procedures. However, it does not include checks to see if the object already exists.

For example, when it attempts to create the Customer table, I would like to have the script check if the table already exists, if it does alter the table.

Is this at all possible?

Answer 1

VS can create a script for just the changes. I think this approach will be better than using existence checks because it will be able to handle column changes, and overall it makes for a shorter and more targeted script.

1. Right-click the project and select Publish.
2. Click Edit and enter the connection details for your staging database.
3. Back on the Publish dialog, click Advanced and make sure "Always re-create database" is not checked.
4. Back on the Publish dialog, click Generate Script.

What this approach does is compare the objects in the database project to your staging database and generates a SQL script for just what is different. You can even save the publish settings to a file to make it easier to generate future scripts.

Answer 2

Keith is right you need to script the changes rather than just using the create statements.

You basically either need a copy of the production database to run a comparison against or you give the DBA's a way to run the comparison and deploy.

The way I prefer to do it is with TFS is to use SSDT in Visual Studio, I then have a custom build step as part of the .sqlproj file that builds the dacpac, uses sqlpackage.exe to compare the dacpac to the mirror of production (or dev, uat, whatever) - this then outputs a script that will take that version of the database to the same version of the code as the dacpac.

You can adjust this slightly to auto-deploy to dev, uat etc and just create the script in production but the choice of exactly what you do it up to you!

If you can't get a mirror of production or a copy of the schema of production etc, you can give the dacpac to the dbas and and either a batch file or powershell script ot drive sqlpackage.exe to create a script or just go ahead and deploy.

Exactly what works depends on the environment you are in!

Ed