VS 2010 Database Project: Building and Deployment
Sun, May 2 2010 22:52
The new Visual Studio 2010 Database Project, as described in this prior post, makes it easy to manage the scripts for your database from within Visual Studio. This includes building the schema scripts from the individual database object scripts and deploying the scripts back to the database.
One of the ways that the new VS 2010 Database project is significantly different from the Database project available in prior versions of Visual Studio is the build and deployment process.
The old VS 2003/2005/2008 Database project was just a container for your scripts. You could right-click on the database in Server Explorer to generate the script to the project. When you made changes to the script, you could right-click on the script and select to apply the script to the database. But you had to manually perform these tasks.
And with the old style Database projects, it was easy to forget to apply your script changes to the database. It also was not easy to keep everything in synchronization. For example, if you later made a table change, it is easy to forget to adjust the related stored procedures as well.
Instead of being a simple repository, the new 2010 Database project is alive. The scripts in your Database project define your database just like the files in your code projects define your application. And the build and deployment process can apply the changes to your actual database.
This post details how to use the VS 2010 Database project and deploy your scripts back to your database.
Start by adding a VS 2010 Database project to your solution following the steps in this prior post. Then write the scripts into your project from an existing database as detailed in this prior post. Or write your own scripts in the project.
After you've made any changes to those scripts, you will want to deploy them back to the database.
To build the Database project, right-click on the Database project and select Build. OR you can use the Build menu.
When you build the Database project, Visual Studio validates your scripts and writes the database schema to a file with a .dbschema extension.
To deploy the scripts to your database, right-click on the Database project and select Deploy. Or you can use the Build menu.
When you deploy the Database project, Visual Studio builds a deployment script.
You can then run that one script to apply all of the information from your database object scripts to the development database.
But if you want to ensure that your development database is always up to date with the scripts in your Database project, you can set your Database project properties so that any changes are automatically deployed to the development database when you run your application (or build the solution).
Double-click on the Properties node of the Database project in Solution Explorer to open the Database project properties. Select the Deploy tab:
Set the Deploy action to "Create a deployment script (.sql) and deploy to the database". Then use the Edit button to the right of the Target connection (cut off in the above screen shot) to set the connection to your development database. Once you set these project settings, they will remain set unless you change them.
Each time you deploy the Database project, using the menu options or by just running the application within Visual Studio, Visual Studio will immediately apply all script changes directly to the database.
In this example, I added a table script for an Invoice table. When I ran the application, Visual Studio built and deployed the Database project scripts, creating the Invoice table in the database.
Use these features of the Database project any time you want to keep the master copy of your database schema defined in your scripts and ensure that the schema is in sync every time you run your application within Visual Studio.