How to: Deploy a Database With a Web Application Project

When you prepare to deploy a Web application project that uses one or more SQL Server databases, you can enter settings that specify database scripts that must run during deployment. These settings apply whether you deploy by using one-click publish or by using a Web deployment package.

The first two procedures in this topic provide instructions for specifying database information to deploy when the database does not already exist in the destination environment. The remaining procedures explain how to change the settings after the application has already been deployed. After you deploy an application the first time, the database will already be on the destination server. You might not want to run database scripts, or you might want to run different scripts.

This topic describes only database settings. For information about other deployment setup tasks, see How to: Deploy a Web Application Project Using One-Click Publish and Web Deploy or How to: Deploy a Web Application Project Using a Web Deployment Package. For more information about Web application project deployment, see the documentation for Visual Studio 2012. A good place to start is ASP.NET Deployment Content Map.

Configuring for First-Time Deployment

You use the Package/Publish SQL tab of the project Properties page to configure settings that determine which scripts will run during deployment. This tab is organized in a master/detail layout. In a grid at the top, you specify databases to deploy. In the rest of the tab, you specify settings that apply to the database you have selected in the grid.

To specify databases to deploy

1. In Solution Explorer, select the project name and then select Properties.

2. In the Properties page, click the Package/Publish SQL tab.

   The Package/Publish SQL tab is displayed, as shown in the following illustration:

   

3. In the Configuration drop-down list, select the build configuration that you want to deploy.

   You can configure deployment for the Debug or Release build configurations, or for a custom build configuration that you have created by using the Configuration Manager dialog box. For example, you might want different settings for deploying to a test server and for deploying to a production server.

   The default value for the Configuration list is Active. This means that the settings on this tab will apply to the build configuration that is currently selected for the project. The currently selected build configuration is shown in the main toolbar and in the Configuration Manager dialog box. (To open Configuration Manager, select it from the Build menu.)

4. Click Import from Web.config.

   A row is created in the grid for each connection string that is found in the Web.config file. By default, "-Deployment" is appended to the connection string name.

5. If the Web application uses databases that are not represented by connection strings in the Web.config file, manually add a row for each of those databases. For rows that you enter manually, you can use any name that helps you remember which database the row applies to.

After you have specified the databases to deploy, you can enter settings for each database. Perform the following steps for each database that you entered in the Database Entries grid.

To enter settings for a database

1. In the Database Entries grid, select the row that you want to enter settings for.

   The values that you enter in the Database Entry Details section apply only to the currently selected row in the Database Entries grid. If no rows are selected in the Database Entries grid, the Database Entry Details section is disabled.

2. In the Connection string for destination database box, enter the connection string to use during deployment.

   This connection string might be different from the one that you use in the deployed Web.config file for the same database. For example, the connection string to use for deployment might need administrator permissions that you do not want the connection to have when users run the application.

3. If you want to automatically generate scripts from an existing database, select the Pull data and/or schema from an existing database check box.

   By default, this check box is selected for rows in the Database Entries grid that were imported from the Web.config file. When this option is selected, the Database Scripts grid includes a row to indicate that automatically generated scripts will run in the destination database.

4. If you selected the Pull data and/or schema from an existing database check box, perform the following steps:

   1. In the Connection string for the source database text box, enter the connection string to use during deployment.

      By default, for rows in the Database Entries grid that were imported from the Web.config file, this field is initialized to the value from the Web.config file.

   2. In the Database scripting options drop-down list, specify whether you want the scripts that are automatically generated for this database to duplicate only the database structures (Schema Only), to duplicate both the structures and the data (Schema and Data), or to duplicate only the data (Data Only) in the destination environment.

      Note

      If you are deploying the default ASP.NET membership database, and if you do not want to deploy the accounts that you created on the development computer, additional steps are required. You cannot simply select Schema Only, because the membership system requires data from one of the tables. For more information, see How to: Deploy the ASP.NET Membership Database Without Including User Accounts.

      Note

      If you select Schema Only or Schema and Data, and if users or roles are created in the database, deployment might fail because of insufficient permissions to create users or roles in the destination database. For more information about this error and how to resolve or prevent it, see the question about CREATE USER and CREATE ROLE database commands in ASP.NET Web Application Project Deployment FAQ.

5. If you want to specify custom database scripts to run in the destination database during deployment, add them to the Database Scripts grid by using the Add Script button.

   When you click Add Script, a Select File dialog box is displayed. This dialog box enables you to select a script that you have written and to add it to the Database Scripts grid.

6. If the rows in the Database Scripts grid are not in the order in which the scripts should run, rearrange the rows by using the up arrow and down arrow buttons.

7. In the Web.config transform file for the build configuration that you are deploying, add markup that will transform the connection string for this database to the value that it should have in the deployed Web application.

   For information about how to create and edit Web.config transform files, see How to: Transform Web.config When Deploying a Web Application Project.

8. If you do not want the automatically generated script to run in a transaction, edit the project file to set the Transacted attribute of the script's Source element to False. For information about how to edit the project file, see How to: Edit Deployment Settings in the Project File.

   By default, the automatically generated script runs in a transaction. If you are deploying a very large database, you might prefer not to run this script in a transaction in order to improve performance. By default, custom scripts do not run in transactions.

   Note

   If multiple scripts will be run for a database, set the same value for the Transacted attribute for all the scripts. Mixing True and False values might cause a time-out error during deployment.

9. If the source or destination database is SQL Azure and if you are running an automatically generated script, edit the script's PreSource element in the project file so that it includes the following attributes:

   targetDatabaseEngineType=SQLAzureDatabase

   targetServerVersion=version100

   prefetchObjects=false

   Set the targetServerVersion to version100 for SQL 2008 or version105 for SQL 2008 R2. For information about how to edit the project file, see How to: Edit Deployment Settings in the Project File.

Redeploying Without Database Changes

After you have deployed a Web application the first time, you might have to deploy it again. The following procedure describes how to disable database updates for all databases or for selected databases when you redeploy the Web application.

To redeploy without database changes

1. In Solution Explorer, right-click the project name and then click Properties.

2. Click the Package/Publish Web tab.

   The Package/Publish Web tab is displayed, as shown in the following illustration:

   

3. Make sure that the build configuration that you want to change settings for is selected in the Configuration drop-down list.

4. If you want to disable updating for all databases that are listed in the Database Entries grid, clear the Include all databases configured in Package/Publish SQL tab check box.

   As a result of clearing this check box, no SQL scripts will be run during deployment.

5. If you want to disable updating for selected databases that are listed in the Database Entries grid, clear the Deploy check box for each database that you do not want to run scripts for.

   As a result of clearing these check boxes, no SQL scripts will be run for the specified databases during deployment.

Redeploying By Using Automatically Generated Scripts

After you have deployed a database by using automatically generated scripts, you might make database changes and then redeploy the database. In that case, the deployment process must drop existing database objects before the objects are re-created. The following procedure describes how to specify that automatically generated scripts should include SQL Drop statements.

To redeploy a database by using automatically generated scripts

• If you want the automatically generated script to drop database objects such as tables, views, and stored procedures individually before it creates new versions of these objects, edit the project file to set the ScriptDropsFirst attribute of the PreSource element of the automatically generated script to True. For information about how to edit the project file, see How to: Edit Deployment Settings in the Project File.

  This setting causes a SQL Drop statement to be generated for each database object that is created from the source database. This Drop statement for a particular object will run just before the statement that creates that object. If an object occurs in the destination database but not in the source database (typically because it was dropped from the source database after the previous deployment), no Drop statement will be generated for it.

  If you want to drop the entire database before re-creating individual database objects, set the DropDestinationDatabase attribute of the Source element to True.

  Note

  Make sure that the credentials that you use have sufficient permissions both to drop and to re-create the database. If the credentials are assigned to the db_owner role but not to the dbcreator role, you might be able to drop the database but not re-create it after it is dropped. If you are deploying to a hosting company, it is unlikely that the credentials you are given will be assigned to the dbcreator role.

Deploying Database Changes by Using Custom Scripts

After you have deployed a database, you might make database changes and then want to deploy only the changes to the destination database. This lets you preserve any data that might have been entered in the destination database. The following procedure describes how to disable automatically generated scripts and add custom scripts that update the database.

To deploy changes to a database and preserve data

1. Create custom database scripts that will make the changes that you want in the destination database.

   You can use database projects to automatically generate scripts that reflect changes to database structure. For more information, see Working with Database Projects.

2. In Solution Explorer, right-click the project name and then select Properties.

3. Click the Package/Publish SQL tab.

   The Package/Publish SQL tab is displayed, as shown in the following illustration:

   

4. In the Database Scripts grid, clear the Include check boxes in the rows for scripts that should not run.

   If you selected the Pull data and/or schema from an existing database check box, clear the Include check box for the automatically generated script.

5. Add the custom database scripts for the selected database by using the Add Script button.

   When you click Add Script, a Select File dialog box is displayed that enables you to select a script that you have written and add it to the Database Scripts grid. By default, the Include check box will be selected.

6. Optionally, if the rows in the Database Scripts grid are not in the order in which the scripts should run, rearrange the rows by using the up arrow and down arrow buttons.

See Also

Tasks

Walkthrough: Deploying a Web Application Project Using a Web Deployment Package (Part 1 of 4)

How to: Deploy a Web Application Project Using One-Click Publish and Web Deploy

How to: Deploy a Web Application Project Using a Web Deployment Package

Concepts

ASP.NET Deployment Content Map