How to: Deploy Changes to New or Existing Databases

  • Article

After you have made changes to the database project, you must deploy those changes to the database server. When you deploy the database project, the schema that you defined in the database project is compared with the target schema in order to identify the set of actions that must occur to synchronize the schemas. Those actions are combined with the pre-deployment script and the post-deployment script to form a complete deployment script. If the target database does not exist or if you chose to always re-create the database, the schema that is defined in the .dbschema file is used to create the database. When you create a new database, the deployment script will attempt to drop an existing database with the same name if one already exists. If the database project, its properties, or its contents have changed since the last build or deployment, then you generate an up-to-date .dbschema file when you build the project.

In a production environment, you would probably not deploy directly to the target database. Instead, you would generate the deployment script (a .SQL file), review and optionally update it, and then deploy it by using the Transact-SQL Editor. Alternatively, you could also use a tool such as SQL Server Management Studio.

You could also deploy your database by using the VSDBCMD.EXE tool. You can specify the.dbschema file that you want to use to deploy the database. You can use VSDBCMD to deploy from a computer that does not have Visual Studio installed. For more information, see How to: Prepare a Database for Deployment From a Command Prompt by Using VSDBCMD.EXE.

Important

Before you deploy the database project, you must set the project properties to specify the target database connection and database name. If you generated the database project by using the New Database Project Wizard, then you might already have specified your project properties. You can change the project properties at any time. For more information, see How to: Configure Database Properties for Database Projects.

Required Permissions

You must have the necessary permissions to create the database (for new deployments) and create, modify, and delete the schema objects in that database to deploy the database project.

Deploying to a Single Database

You can deploy your database by using Visual Studio, from a command prompt, or by running the deployment script in a Transact-SQL editor. You can use the Transact-SQL editor provided in Visual Studio, or you can use a tool such as SQL Server Management Studio.

To deploy the database project to a new or existing database

  1. In Solution Explorer, click the database project that you want to deploy.

    If you created a custom configuration, you can specify that it should be used by clicking its name in the Solution Configurations list on the Standard toolbar. By default, you have a single configuration for your database project, and it is already selected.

  2. On the Build menu, click Deploy.

    The build output for the database project is generated (if it does not exist or if it is not up to date) and then deployed. The Output window displays the results of the deployment, which appear as follows:

    ProjectName -> D:\Documents and Settings\UserName\My Documents\Visual Studio 2008\Projects\SolutionName\ProjectName\sql\ProjectName.dbschema

    ------ Deploy started: Project: ProjectName, Configuration: Debug Any CPU ------

    Deployment script ProjectName.sql generated

    Creating ProjectName

    Creating dbo.ObjectName

    .

    .

    .

    .

    ========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

    ========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

To deploy the database project to a new or existing database from the command line

  1. Open a Command Prompt window, and browse to your project directory.

  2. At the command prompt, type the following example command line to deploy a database project (where DatabaseProject is the name of your project):

    MSBuild /t:Build;Deploy DatabaseProject.dbproj

    Note

    MSBuild.exe must be on the path that your PATH environment variable specifies. In addition, you must specify the TargetDatabase and TargetConnectionString either in the database project or as part of the command line. For more information about command line syntax for generating build scripts, see An Overview of Database Build and Deployment.

    Results such as the following will appear:

    Microsoft (R) Build Engine Version 3.5.21022.8

    [Microsoft .NET Framework, Version 2.0.50727.1433]

    Copyright (C) Microsoft Corporation 2007. All rights reserved.

    Build started 5/20/2008 1:36:58 PM.

    Project "ProjectDrive:\Documents and Settings\UserName\My Documents\Visual Studio 2008\Projects\SolutionName\ProjectName\ProjectName.dbproj" on node 0 (Deploy target(s)).

    Project "ProjectDrive:\Documents and Settings\UserName\My Documents\Visual Studio 2008\Projects\SolutionName\ProjectName\ProjectName.dbproj" (1) is building "ProjectDrive:\Documents and Settings\UserName\My Documents\Visual Studio 2008\Projects\SolutionName\ProjectName\sql\ProjectName.deploymanifest" (2) on node 0 (DspDeploy target(s)).

    Deployment script ProjectName.sql generated

    :

    (1 row(s) affected)

    Done Building Project "ProjectDrive:\Documents and Settings\UserName\My Documents\Visual Studio 2008\Projects\SolutionName\ProjectName\sql\ProjectName.deploymanifest" (DspDeploy

    target(s)).

    Done Building Project "ProjectDrive:\Documents and Settings\UserName\My Documents\Visual Studio 2008\Projects\SolutionName\ProjectName\ProjectName.dbproj" (Deploy target(s)).

    Build succeeded.

    0 Warning(s)

    0 Error(s)

    Time Elapsed 00:00:07.32

To deploy the database by running the deployment script

  1. Open the deployment script in the Transact-SQL editor of your choice.

    Note

    This procedure assumes that you are using the Transact-SQL that comes with Visual Studio.

    The compiled deployment script is named ProjectName.sql and is in the SolutionName\ProjectName\sql\Configuration folder. Configuration is the name of your build configuration, such as Debug or Release.

  2. On the menu bar, choose Data, Transact-SQL Editor, SQLCMD Mode if it is not already enabled.

    Important

    If SQLCMD mode is not enabled, errors will occur when you run the deployment script because the :setvar statements are not supported.

  3. On the menu bar, choose Data, Transact-SQL Editor, Execute SQL to run the deployment script. When prompted, specify the connection information for the server to which you want to deploy.

    The deployment script runs and your database is created.

Deploying to Multiple Databases or Multiple Servers

You can deploy your schema to multiple databases or multiple servers. If you are deploying to a small number of servers or databases that do not change (for example, if you want to deploy to both your personal development server and a staging server), you can create multiple build configurations. Each build configuration can have a different target server or database. If you need to deploy to a larger number of servers or if the names of the target server or database change on a regular basis, you should use a different approach. These approaches are described below.

To create multiple build configurations to deploy to multiple servers or databases

  1. Open your database project in Visual Studio.

  2. On the Build menu, click Configuration Manager.

    The Configuration Manager dialog box appears.

  3. In the Active solution configuration list, click <New>.

    The New Solution Configuration dialog box appears.

  4. In Name, type the name for your first server configuration.

  5. If you had project properties already configured in the Default configuration, in the Copy settings from list, click Default.

  6. Click OK.

  7. In the Active solution configuration list box, click <New>.

    The New Solution Configuration dialog box appears.

  8. In Name, type the name for your second server configuration.

  9. If you had project properties already configured in the Default configuration, in the Copy settings from list, click Default.

    Note

    You can also configure the database name and connection string for each named configuration.

  10. Click Close.

  11. Right-click your database project in Solution Explorer, and click Properties.

  12. Click the Build tab.

  13. In Configuration, click the name of your first server configuration.

  14. Modify the target connection and the database name to match your first server configuration.

  15. In Configuration, click the name of your second server configuration.

  16. Modify the target connection and the database name to match your second server configuration.

  17. On the File menu, click Save Selected Items to save your configuration settings.

    Now you can specify a configuration on the Standard toolbar and then build or deploy that configuration.

To deploy to databases from the command line by using MSBuild

  • In a Command Prompt window, deploy the build script by typing the following command line (replacing the server name and the target configuration with the appropriate values).

    MSBuild /t:Deploy /p:Configuration=TargetConfiguration DatabaseProjectName.dbproj

    Note

    To deploy to multiple servers, you must override the target connection string property by specifying the following in a Command Prompt window: /p:TargetConnectionString="Data Source=ServerName;Integrated Security=True;Pooling=False". You could define one configuration for each target server and then specify the configuration that you want to deploy.

To deploy a database project from a command prompt by using VSDBCMD

  • Important

    This procedure assumes that you have already copied the necessary files to the computer from which you want to run VSDBCMD. For more information, see How to: Prepare a Database for Deployment From a Command Prompt by Using VSDBCMD.EXE.

    Open a Command Prompt window, and type the following command line:

     VSDBCMD /a:Deploy /dd:+ /manifest:manifestFileName.dbmanifest

    The deployment manifest provides the other settings, such as target database name, connection string, and so on, that you could otherwise specify on the command line.

See Also

Tasks

How to: Build a Database Project to Generate a Compiled Schema (.dbschema) File

Walkthrough: Deploy Changes to an Existing Version-Controlled Database

Concepts

An Overview of Database Build and Deployment

An Overview of Database Project Settings