An Overview of Database Build and Deployment

  • Article

To create a database or to publish updates to an existing database from your database project to a database server, you must build the database project and then deploy it to that server.

The build step compiles your database project into the files that are used for deployment. These files include the .dbschema file, the .deploymentmanifest file, and the pre-deployment and post-deployment scripts. No comparison with a target database is performed when you build the database project. When you deploy the database project, the output of the build action is compared to the target database (if it exists), and a deployment script is generated. Depending on your settings, the script is deployed to the target database.

As an alternative, you can generate the deployment script and then modify it before you deploy it to a staging or production server. The Clean build action deletes any existing build artifacts, such as scripts, deployment manifests, and the .dbschema files.

Deployment Scripts

You can create scripts that run before or after the scripts that create or update the target. You can have only one pre-deployment script and one post-deployment script, but you can include other scripts from within these scripts. For more information, see Scenario: Create and Modify Database Scripts.

Deployment Manifest

When you build your database project, a deployment manifest is generated. This manifest contains all of the project-level configuration information that is required to enable you to deploy without also needing the database project file (.dbproj).

A deployment manifest file (.deploymanifest) is an XML file whose structure is very similar to an MSBuild project file. The .deploymanifest file is created in the sql\Configuration folder in your database project folder, where Configuration is the build configuration, such as debug or release.

The following example shows the .deploymanifest file for an empty SQL Server 2008 database project named Empty2008DbProj:

<Project ToolsVersion="3.5" xmlns="https://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <TargetDatabase>Empty2008DbProj</TargetDatabase>
    <DeployToDatabase>False</DeployToDatabase>
    <DeployToScript>True</DeployToScript>
    <SourceModel>Empty2008DbProj.dbschema</SourceModel>
    <DeployScriptFileName>Empty2008DbProj.sql</DeployScriptFileName>
    <DeploymentConfigurationFile>Database.sqldeployment</DeploymentConfigurationFile>
  </PropertyGroup>
  <ItemGroup>
    <DeploymentExtensionConfiguration Include="Script.PostDeployment.sql">
      <__PostdeploymentMetadata>
      </__PostdeploymentMetadata>
    </DeploymentExtensionConfiguration>
    <DeploymentExtensionConfiguration Include="Script.PreDeployment.sql">
      <__PredeploymentMetadata>
      </__PredeploymentMetadata>
    </DeploymentExtensionConfiguration>
  </ItemGroup>
  <ItemGroup>
    <Reference Include="Microsoft.SqlTypes.dbschema" />
  </ItemGroup>
  <PropertyGroup>
    <SqlCommandVariablesFile>Database.sqlcmdvars</SqlCommandVariablesFile>
  </PropertyGroup>
</Project>

The deployment manifest file contains one or more PropertyGroup nodes that define the default configuration that is used when you deploy. The deployment manifest file also includes ItemGroup nodes that contain DeploymentExtensionConfiguration nodes and Reference nodes.

DeploymentExtensionConfiguration nodes define configuration files that are passed to deployment extensions when you deploy. These configuration files include the pre-deployment and post-deployment scripts, as well as the refactoring log that is used to preserve intent when you deploy.

Reference nodes define any artifacts that are referenced by the project and that are copied into the output folder when you build the project. The referenced files are processed when you deploy to ensure that the model of the database is correctly re-created.

Transactions in the Deployment Script

Most database changes in the deployment script occur inside a transaction so that the changes can be rolled back if deployment fails. However, the following objects are created, updated, or deleted outside the deployment transaction.

SQL Server Version

Objects Outside the Deployment Transaction

SQL Server 2008

Server Role Membership

Endpoints

Full-text Indexes

Full-text Catalogs

Linked Servers

Linked Server Logins

Event Sessions

Server Audit Specifications

Full-text Stop Lists

Microsoft SQL Server 2005

Server Role Membership

Endpoints

Full-text Indexes

Full-text Catalogs

Linked Servers

Linked Server Logins

SQL Server 2000

Role Membership

Server Role Membership

Full-text Indexes

Full-text Catalogs

Linked Servers

Linked Server Logins

Application Roles

Logins

Users

Roles

Typically, these objects must be outside the deployment transaction because they are maintained by using system stored procedures.

Considerations for Deploying Changes to an Existing Database

When you deploy changes to an existing database, some changes could cause data loss. If a change might cause data in a table to be lost, the deployment will be canceled if the Block incremental deployment if data loss might occur check box is selected. By default, this check box is selected, but you can find it in the Properties window for your project. For more information, see An Overview of Database Project Settings.

The following kinds of changes will cause data loss: if a table is dropped and recreated, if you change the size of a column (char(100) to char(50) or nchar(100) to char(100)), or if you change the collation of a column that has a character type.

Note

When you use refactoring to rename a database object or to move a database object to another schema, the refactoring log file records that action. At deployment time, the information in the log file helps preserve the intent of your changes. For example, you might lose data if you renamed a table, because the table would be dropped and a table would be created with the new name. With the refactoring log file, the deployment script can instead rename the table, preserving your intent and your data.

Recovering from Failed Deployments

Visual Studio Team System Database Edition creates a save point in the transaction log so that you can revert to that save point if deployment fails. However, you can lose data if the data in the database is modified during deployment between when the save point was created and when the database was reverted. For this reason, you should strongly consider putting a shared database in single-user mode and backing it up before you deploy it.

Note

You must use the full recovery model to restore a database from a failed deployment. The simple recovery model is not sufficient because the transaction log is required. For more information, see this topic on the Microsoft Web site: Recovery Model Overview.

Excluded Files

If you exclude files from your database project, the database objects that are defined within those files will not be included in your build or deployment. If you are still working on one or more objects but want to deploy work that is already complete, you can exclude files to deploy only those items that are ready. You can later include the files when they are ready to be deployed, and deployment will update the database with the new objects without modifying the existing objects (if they have not been changed in your project). For more information, see How to: Exclude Files from a Database Project.

Important noteImportant Note:

You can lose data if you exclude objects from your database project that exist in the target database and then deploy the project. Those objects will be deleted from the target database if the Generate DROP statements for objects that are in the target database but that are not in the database project check box is selected in the deployment configuration.

Server Projects

Server projects contain definitions for objects in the "master" database. The name of the target database for a server project is always "master". For each server setting, you can specify whether you want to verify the value of that setting when you deploy the server project. Settings that you do not verify are ignored. If the value of a server setting does not match the value of a setting that you want to verify, deployment fails with an error message.

Command-Line Builds

In addition to performing the build, deploy, or clean actions from within the Visual Studio user interface, you can also perform these actions at a command prompt by using MSBuild.exe. You can specify Build, Deploy, Rebuild, Clean, SQLBuild, SQLDeploy, and CleanProject targets. By default, the build and deploy processes use the project properties that are defined within the database project (in the .dbproj file or the .dbproj.user file). However, you can override these properties at a command prompt or from within a response file.

Important noteImportant Note:

You should close Visual Studio before you perform a command-line build. If you perform a command-line build when Visual Studio is running, some errors might not be caught.

Command-Line Syntax

You can build the database project at a command prompt by using the following examples of syntax:

  • MSBuild /target:Build MySolutionName.sln
    This example performs the build action on the solution that is named MySolutionName.sln by using the project properties that are specified in the project files that the solution contains. If the solution contains multiple database projects, they are built together with everything else in the solution. The build action will generate the script that is required to create or update the target database, but the script will not be deployed. You can also build a specific database project. The Build target includes pre-deployment and post-deployment scripts in the generated build script.

  • MSBuild /target:SQLBuild MyProjectName.dbproj
    This example also performs a build-only action. You can use this syntax to build only a single database project that is named MyProjectName.dbproj. The project properties within the .dbproj file are used to assemble the build script. The script is not deployed. The SQLBuild target does not include the pre-deployment or post-deployment scripts in the generated build script.

  • MSBuild /target:Build /p:BuildScriptName=MyScriptName.sql MyProjectName.dbproj
    This example also performs a build-only action. You can use this syntax to build only a single database project that is named MyProjectName.dbproj. The project properties within the .dbproj file are used to assemble the build script. The command line overrides the BuildScriptName property and specifies a different name for the output script. You might use this approach if you want to deploy the build script to multiple servers. The script is not deployed.

  • MSBuild /target:Deploy /property:TargetDatabase=UpdatedTargetDatabase;TargetConnectionString="Data Source=(local)\SQLEXPRESS;Integrated Security=True;Pooling=False" MyProjectName.dbproj
    This example demonstrates how to deploy the database project while overriding the target database name and connection string.

  • MSBuild /target:Deploy /p:DeploymentConfiguration=DeployPath\AlternateDeploymentConfiguration.deploymentconfig /p:SqlCommandVars=VarPath\AlternateVariables.sqlcmdvars /property:TargetDatabase=UpdatedTargetDatabase;TargetConnectionString="Data Source=(local)\SQLEXPRESS;Integrated Security=True;Pooling=False" MyProjectName.dbproj
    This example demonstrates how to deploy the database project while specifying a different target database and connection string and overriding the deployment configuration and deployment variables.

  • MSBuild /target:Deploy /property:BuildScriptName=MyScriptName.sql /property:outdir=BuildScriptPath /property:TargetDatabase=UpdatedTargetDatabase;TargetConnectionString="Data Source=InstanceName\DatabaseName;Integrated Security=True;Pooling=False" ProjectPath\MyProjectName.dbproj
    This example demonstrates how to deploy a database from a computer other than the one on which the build occurred. For example, you might use this syntax if you have a central build computer that creates a build script every night. You must specify the name of the build script, the path where the build script can be found (outdir), the target database, and the path and file name of the database project.

  • MSBuild @dbbuild.arf MyProjectName.dbproj
    This example demonstrates how to use a response file to provide command-line arguments. The file, dbbuild.arf, can contain any valid arguments for MSBuild, including those that override project properties.

  • MSBuild /target:Rebuild MyProjectName.dbproj
    This example rebuilds the specified project or solution, even if it has not changed since the last time it was built.

  • MSBuild /target:Clean MyProjectName.dbproj
    This example demonstrates how to delete any existing build scripts. In most cases, you would follow this action with another build or deploy action.

Note

You can abbreviate /target: as /t: and /property: as /p:.

For more information, see MSBuild Command Line Reference.

For more information about response files, see this topic on the Microsoft Web site: MSBuild Response Files.

Note

To run MSBuild.exe, you must either use the Visual Studio Command Prompt, or you must run the vsvars32.bat batch file. You can find this batch file in the folder that the %VS80COMNTOOLS% environment variable specifies.

Project Properties

Some properties of database and server projects affect how those projects will be built and deployed. These properties are stored within the project file and the .user file, but you can override them at a command prompt or in a response file. For more information, see How to: Configure Build Settings for Database and Server Projects and How to: Configure Deployment Settings for Database and Server Projects.

Overriding Deployment Manifest Properties

You can override the default deployment properties (such as deployment configuration, connection string, or SQLCMD variables) when you deploy from the command line. You might choose to do this if you want to deploy a .dbschema file into multiple environments.

For example, if you want to deploy a schema defined in EnterpriseDB.dbproj into development, test, and production environments, you could use the following command lines:

Deployment Environment

MSBuild EnterpriseDB.dbproj /t:Deploy /p:DeploymentConfigurationFile=sql\debug\Development.sqldeployment /p:SqlCommandVariablesFile=sql\debug\Development.sqlcmdvars /p:TargetConnectionString="Data Source=DEV\sql2008;Integrated Security=true;Pooling=false"

Test Environment

MSBuild EnterpriseDB.dbproj /t:Deploy /p:DeploymentConfigurationFile=sql\debug\UserTest.sqldeployment /p:SqlCommandVariablesFile=sql\debug\UserTest.sqlcmdvars /p:TargetConnectionString="Data Source=USERTEST\sql2008;Integrated Security=true;Pooling=false"

Production Environment

MSBuild EnterpriseDB.dbproj /t:Deploy /p:DeploymentConfigurationFile=sql\debug\Production.sqldeployment /p:SqlCommandVariablesFile=sql\debug\Production.sqlcmdvars /p:TargetConnectionString="Data Source=PRODUCTION\sql2008;Integrated Security=true;Pooling=false"

In each environment, you provide a different deployment configuration file, a different SQLCMD variables file, and a different connection string.

See Also

Tasks

How to: Prepare Database Build Scripts

How to: Deploy Changes to New or Existing Databases

Walkthrough: Create and Deploy a New Version-Controlled Database

Walkthrough: Deploy Changes to an Existing Version-Controlled Database

Concepts

Build and Deploy Databases to an Isolated Development Environment

Build and Deploy Databases to a Staging or Production Environment

Terminology Overview of Database Edition