How to: Deploy Database Refactoring Changes

When you work in Microsoft Visual Studio Team Edition for Database Professionals, you change an offline representation of the database. To commit those changes to a live database, you must build and deploy the database project. The process is the same whether you are deploying your changes to a development database, a test database, or a production database.

To set the deployment properties for the project

1. In Solution Explorer, click the database project.

2. On the Project menu, click YourSolution Properties.

   The project properties appear.

3. Click the Build tab.

4. Click Edit to display the Connection Properties dialog box.

5. Set the connection properties for the database where you want to work, and then click OK.

   The Target Connection box is filled with the correct connection string.

6. Type the name of the target database in the Target database name text box.

   Warning

   By default, the Target database name box is populated with the name of the database project. Make sure that you change this field if the database project name is not the target database name.

7. (Optional) Type the path in which you want database files to be created on the target server in the Default location for target database files text box. You can click Refresh to retrieve the default location from the target server.

8. (Optional) Select or clear the Generate DROP statements for objects that are in the target database but that are not in the database project check box.

   This check box is important when, for example, you rename a table. If you select this check box and then generate a deployment script, it will contain a DROP statement for the table with the old name and a CREATE TABLE statement for a table with the new name. You can clear the Generate DROP statements for objects that are in the target database but that are not in the database project check box to prevent the table with the old name from being dropped. This approach is one of several for preventing possible data loss in this situation. For more information, see Protecting Data during a Renaming Operation.

9. Set other properties as appropriate. For more information, see An Overview of Database Project Settings.

10. On the File menu, click Save All.

To build a database project

• On the Build menu, click Build Solution.

  The deployment script is built based on the project properties that you have set. The status of the build appears in the Output window, and Build: 1 succeeded or up-to-date should appear as the last line.

To deploy a database project

1. (Optional) Open the deployment script from the following location:

   <YOUR PATH>\<YOUR SOLUTION>\Sql\\<YOUR PROJECT>.<YOUR SERVER>.<YOUR DATABASE>.sql

2. (Optional) Change the deployment script. For example, you can delete DROP statements that you do not want to deploy. For more information, see Protecting Data during a Renaming Operation.

3. If you edit the script, save and close it after you finish.

   Warning

   If you change the deployment script, you must deploy it manually, by running it from the Transact-SQL (T-SQL) editor. You cannot deploy an edited script by using the deploy command.

4. In Solution Explorer, click the database project.

5. On the Build menu, click Deploy. You can also right-click the project in Solution Explorer and click Deploy.

   The database project is deployed to the target database by using the deployment script. The status of the deployment appears in the Output window, and Deployment succeeded should appear as the last line.

See Also

Tasks

Walkthrough: Renaming a Database Column

Other Resources

Refactoring Databases
Building and Deploying Version-controlled Databases