Share via

An Overview of Database Build and Deployment

  • Article

To deploy a new database or changes to an existing database from your database project to a database server, you must build the database project and then deploy it to your database server. The build step assembles the object definition scripts and any pre-deployment or post-deployment scripts into a single build script, by using properties that you set in the project properties window. The deployment step both assembles the script and then executes that build script against the target that you specify. The clean build action just deletes any existing build scripts. If you are going to update a staging or production server, you will likely build the database project and then modify the build script yourself. After you have made your adjustments and are satisfied with the result, you can then manually deploy that modified build script into the production environment.

Deployment Scripts

You can specify 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 Working with Database Scripts.

You can run a build script against different target databases, each of which can have a different configuration. Therefore, the build script contains statements to set all the database properties, even if their values already match those of the database project properties.

Considerations for Deploying to an Existing Database

When you deploy changes to an existing database, some changes might cause data loss. If this change would cause the loss of the data that the table contains, the deployment will be canceled unless you have cleared the Block incremental deployment if data loss might occur check box in the project properties window. For more information, see An Overview of Database Project Settings. Examples of changes that will cause data loss are as follows: 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 character type column.

Recovering from Failed Deployments

Team Edition for Database Professionals creates a save point in the transaction log and can revert to that save point if deployment fails. However, data can be lost if data in the database is modified during deployment between when the save point was created and when the database was reverted. For this reason, if you are deploying to a shared database, you should strongly consider putting the database in single-user mode 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 about recovery models, see Overview of the Recovery Models.

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).

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 as part of a command-line build 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 on the command line or from within a response file.

Important

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

Command-line Syntax

You can build the database project from the command line by using straightforward syntax, as the following examples show:

  • MSBuild /target:Build MySolutionName.sln
    This example performs the build action on the solution named MySolutionName.sln by using the project properties that are specified in the project files that the solution contains. If the solution contains one or more database projects, they will be built together with everything else in the solution. The build action will generate the script that is necessary 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. It builds only a single database project 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. It builds only a single database project named MyProjectName.dbproj. The project properties within the .dbproj file are used to assemble the build script. The command line overrides BuildScriptName property and specifies a new 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, overriding the target database name and connection string.
  • 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 shows how to deploy a database from a computer other than the one on which the build occurred. For example, you might use this command line 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 name of the database project file.
  • MSBuild @dbbuild.arf MyProjectName.dbproj
    This example shows how to use a response file to provide command-line arguments. The file, dbbuild.arf, can contain any valid MSBuild command-line switches, including those that override project properties. For more information about response files, see MSBuild Response Files.
  • 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. More commonly, you would probably follow this action with another build or deploy action.

Note

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

For more information about command-line options for MSBuild, see MSBuild Command Line Reference.

For more information about response files, see MSBuild Response.

Note

To run MSBuild.exe, you must either use the Visual Studio 2005 Command Prompt or you must run the vsvars32.bat batch file found in the folder specified by the %VS80COMNTOOLS% environment variable.

Database Project Properties

Some database project properties affect how your database project will be built and deployed. These properties are stored within the database project file and the .user file, and you can override them with a command line or in a response file. For more information about database project properties, see An Overview of Database Project Settings.

Security Considerations

If other users can modify your instance of the design-time validation database, they can add or change objects that will end up in your build script. The build output will produce these objects (for example, DDL triggers), and they will run in the context of the person who deploys the database. These objects will not necessarily appear in Solution Explorer or Schema View. Schema Compare will not detect anything when you compare the database project to your deployed database because the design-time validation database also contains those objects.

To minimize risk to your database:

  • Do not share your instance of the design-time validation database with other users.

  • Do review your build scripts before you deploy them.

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

Terminology Overview of Team Edition for Database Professionals