Walkthrough: Deploying Database Refactoring Changes

When you work in Visual Studio Team System Database Edition, 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 similar whether you are deploying your changes to a development database, a test database, or a production database.

Note

The deployment process is similar for all kinds of databases. However, the person who performs the deployment might be different. For example, in some environments, only database administrators (DBAs) have permissions to deploy to the production database.

In a previous walkthrough, Walkthrough: Renaming a Database Column, you created a database project, imported the Northwind database schema, and renamed a column. In this walkthrough, you build and deploy that change.

Prerequisites

To complete this walkthrough, you will need:

• Database Edition.

• Microsoft SQL Server 2000 or SQL Server 2005.

• The database project that you created in Walkthrough: Renaming a Database Column.

To build the database project

1. Open the database project, named RefactorNorthwind, which you created in Walkthrough: Renaming a Database Column.

2. On the Build menu, click Build Solution.

3. Build errors might appear in the Error List window. For example, if a stored procedure selects a column without qualifying it and you rename that column, the name is not updated in the stored procedure. This situation causes a build error because the stored procedure now selects a column name that does not exist. To prevent these types of errors, make sure that column names are fully qualified in Transact-SQL (T-SQL) code. To fix these types of errors, you must manually update the column name. For example, if you rename the column Orders.ShippedDate to Orders.ShippedDateAndTime, you must update the stored procedure Sales by Year manually. Change the stored procedure from this:

   create procedure "Sales by Year" 
       @Beginning_Date DateTime, @Ending_Date DateTime AS
   SELECT Orders.ShippedDateAndTime, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy, ShippedDate) AS Year
   FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
   WHERE Orders.ShippedDateAndTime Between @Beginning_Date And @Ending_Date
   

   To this:

   create procedure "Sales by Year" 
       @Beginning_Date DateTime, @Ending_Date DateTime AS
   SELECT Orders.ShippedDateAndTime, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy, Orders.ShippedDateAndTime) AS Year
   FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
   WHERE Orders.ShippedDateAndTime Between @Beginning_Date And @Ending_Date
   

To set the deployment properties for the project

1. In Solution Explorer, click the database project RefactorNorthwind.

2. On the Project menu, click RefactorNorthwind Properties.

   The project properties appear.

3. Click the Build tab.

4. Click the button that is labeled ... 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.

   Warning

   You should test this deployment against a test database or a development database. You should not test this deployment against your production database.

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

   Warning

   By default, the Target database name box is populated with the name of the database project. You must change this field if the database project name is not the target database name.

7. Check 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, for example, when you rename a table. The deployment script that is generated 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.

8. On the File menu, click Save All.

9. On the Build menu, click Build Solution.

   The deployment script is built based on the project properties that you have just 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 the database project

1. On the File menu, point to Open, and then click File.

   The Open File dialog box appears.

2. In the File name box, type the following address, and then click Open.

   YourPath\RefactorNorthwind\Sql\RefactoringDeploy.YourServer.Northwind.sql

3. In the deployment script, find the following lines:

   DROP TABLE [dbo].[Orders]

   and

   CREATE TABLE [dbo].[Orders]

   These lines correspond to the refactoring change that you made in the previous walkthrough. At this point, you can 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. For the purposes of this walkthrough, you can leave the deployment script the way that it is.

   Warning

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

4. Close the deployment script.

5. On the View menu, click Server Explorer.

   The Server Explorer window appears, and the target database appears under Data Connections. If the target database does not appear, open the Tools menu, and click Connect to Database to add a connection to the target database.

6. In Server Explorer, expand the target database, expand the Tables node, and expand the Orders table.

   The ShippedDate column appears with the original name.

7. In Solution Explorer, click the database project RefactorNorthwind.

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

   Warning

   You should run this deployment against a test database or a development database. You should not run this deployment against your production database.

   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.

9. In Server Explorer, right-click the Tables node, and then click Refresh.

   The ShippedDate column appears with the new name ShippedDateAndTime.

See Also

Concepts

An Overview of Database Project Settings

Other Resources

Refactoring Databases

Building and Deploying Database Schemas