CODEWITHSUNDEEP
sqldatabasevisual-studio-2010
Buzzby
Buzzby

Reputation: 291

Visual Studio 2010 Database Project Installer / Install script

I have been using a database project in visual studio 2010 to create a database. Visual Studio makes this process really easy to deploy the database and deploy updates. The issue I have is how I can get visual studio to create a script that will build the database that I can use on a QA or production system.

I thought that building the project would create a SQL file that did this but this does not seem to be true. Can someone please advise on how I can do this or what simple step I am clearly missing.

Upvotes: 2

Views: 2025

Answers (1)

Dan Nolan
Dan Nolan

Reputation: 4772

As strange as it may sound, Visual Studio does not create a SQL file to deploy your database when you perform a Build.

Instead, this occurs at Deploy time. The reason behind this lies in the way that Visual Studio makes changes to the database that you're deploying to: in order to change your database, it must first compare the project model (as defined in the files contained within the .dbproj) with that of the existing database schema to determine what changes you have made since the database was last deployed.

You then have the choice of manually executing the SQL script produced using the SQLCMD command-line utility (or through SSMS in SQLCMD mode), or alternately you can configure your Visual Studio Database Project to execute the script if you specify the following Deploy Action in project settings:

Generate a deploy script (.sql) and deploy to the database

Note that you will need to provide a connection string in project settings so VS knows where to deploy to.

When you get to the stage where you want to deploy to a QA or PROD environment, you have a couple of options:

  • Deploy directly within Visual Studio. In addition to the standard Debug and Release configurations that you get when you first create your Database Project, you can also create your own configurations which you could use to deploy to your QA and PROD environments (i.e. potentially with a different connection string/target database name).
  • Deploy from a Build Server. Install Visual Studio on a separate machine and call MsBuild against your .dbproj/.sln file with the Build and Deploy targets specified along with the appropriate connection properties.

Doug Rathbone has written a great blog post on subject if you want to learn more: http://www.diaryofaninja.com/blog/2011/06/23/compare-amp-update-database-schemas-right-within-your-ide--part-2-automation-with-teamcity

Upvotes: 1

Related Questions