Deploy Visual Studio 2010 Database Project

Question

I have a Visual Studio 2010 Database project, from which I want to generate a script that simply puts up this database to another machine. The problem is that i can't find a solution for this.

As I started the project, I imported the shema from a database on my development pc. The Schema Objects were generated and all tables and scripts where under 'Schema Objects -> Schemas -> dbo'. Over the time, some things changed, some where added. And by using right-click -> deploy, the changes were made to my local database successfully.

But now I want to deploy to another machine. The problem is, that in the release folder of the project, there is only a xml dbschema file containing all tables and scripts that i can't import with sql management studio (or i just can't find out how) and the a deployment script which is nothing more than some checks followed by the pre- and post- deployment script, but without any tables or scripts in it.

So please, how do i export the database from Visual Studio, so i can easily put it up on another machine?

Answer 1

Marks--

You likely have already resolved this, but I thought I should answer your questions for the benefit of others.

Yes, you can deploy from Visual Studio to different machines. You can also do it from the command line, using VSDBCMD. And you can create a WIX project to give a wizard for others to install it with.

If you can connect to the target database from your dev PC, you can deploy to it. To do this:

1. Select another Configuration from the Solution Configuration drop down. Normally, the Project will come with "Debug" and "Release" baked in. You can add another configuration to allow you to deploy to various targets by clicking "Configuration Manager."

2. Right-click your Project and select 'Properties', or simply double-click Properties under the project.
3. Click the Deploy tab. Notice that the Configuration: drop-down shows the same selected configuration as "active."
4. Change the Deploy Action to "Create a deployment script (.sql) and deploy to the database."
5. Next to Target Connection String, click "Edit" and use the dialog to create your deployment connection to the target database.
6. Fill in the Target database name, if different.
7. For each Deployment Configuration (e.g., Debug, Release, etc.), you will probably want a separate Deployment configuration file. If you click "New," you can create one for the current configuration. The new file will open, and you can check and uncheck important things about the deployment.

8. Note: If you check Always re-create the database, the script will DROP and CREATE your database. You will lose all your data on the target! Be careful what you select here. Most people leave that unchecked for a Production target. I check it for Development or Local because I want a fresh copy there.
9. Save your changes to the file and to Properties.
10. To deploy to the target, be sure to select the correct Configuration. Click Build/Deploy [My Database Name]. You probably should experiment with this so you are familiar with how it works before trying it on a live environment.
11. Good practices: build a similar environment to production ("Staging") and deploy there first, to test the deployment, and always back up the database before deploying, in case something goes wrong.

For more info, please see:

• Working with Database Projects
• Walkthrough: Put an Existing Database Schema Under Version Control
• Visual Studio 2010 SQL Server Database Projects

Answer 2

Is it's possible to point your Visual Studio to your new target database? 1. Properties of your Database project, Deploy tab, set the fields in Target Database Settings.

Now when you generate a deploy script, the resulting SQL file will be the various CREATe / ALTER / DROP etc that will align the target database with your schema.

Answer 3

You could always create an empty database and then do a schema compare in Visual Studio between your database project and the new empty database. You can amend the generated schema update script to also create the database (since the script will be to update an existing empty database)