Protecting Data during a Renaming Operation

  • Article

When you rename a database object and then build the database project, a deployment script is generated that reflects that change. When you rename a column or table, the script drops the original column or table with the original name and adds the new column or table with the new name. If you deploy this change to the existing database as an update, the data in the original column or table is lost.

You can control whether the original column or table is dropped by setting the appropriate option in the project properties. The Build tab of the project properties contains a check box that is named Generate DROP statements for objects that are in the target database but that are not in the database project. If you select this check box, the drop statement for the old object is included in the deployment script that is generated when you build the project. If you clear this check box, the drop statement for the old object is not included in the deployment script that is generated when you build the project.

Using sp_rename

You can manually edit the build script to convert the column and table drop/add to sp_rename.

For example, you can specify the following for a table:

sp_rename '<OLD TABLE NAME>', '<NEW TABLE NAME>', 'OBJECT'

For example, you can specify the following for a column:

sp_rename '<TABLE NAME>.<OLD COLUMN NAME>', '<NEW COLUMN NAME>', 'COLUMN'

For more information, see How to: Deploy Database Refactoring Changes.

Moving Data between the Old and New Objects

To prevent the potential data loss that the drop statement might cause, you can choose not to deploy the drop statement. You will have copies of both the old object and the new object in the target database. You can move the data from the old object to the new object and then drop the old object. You can accomplish this task by using one of the following methods:

  • Use a select statement or other T-SQL statement to move the data manually.

    For example, you can specify the following for a table:

    -- Deploy table add only.

-- Move the data between the old and new tables.
INSERT Region_New SELECT * FROM Region_Old

-- Drop the old table manually.
DROP TABLE Region_Old

    For example, you can specify the following for a column:

    -- Deploy column add only.

-- Move the data between the old and new columns.
UPDATE Region SET ColumnNew = ColumnOld

-- Drop the old column manually.
ALTER TABLE Region DROP COLUMN ColumnOld

  • Use bcp Utility, Data Transformation Services (SQL 2000), or SQL Server Integration Services (SSIS) (SQL 2005) to move the data automatically.

Moving Data by Using Temporary Storage

To prevent the potential data loss that the drop statement might cause, you can move your data to temporary storage. Before you deploy the change, you move the data from the old table to a temporary location. For example, you could move the data to a table on a different database or to a data file. Then you deploy the drop and the add statements. Finally, you move the data from the temporary location to the new table. You can accomplish this task by using one of the following methods:

  • Use a select into or other T-SQL statement, to move the data manually.

    For example, you can specify the following for a table:

    -- Move the data to temporary storage.
SELECT * INTO TemporaryStorage.dbo.Region_Storage FROM Region_Old

-- Deploy table drop/add.

-- Move the data to the new table.
INSERT Region_New SELECT * FROM TemporaryStorage.dbo.Region_Storage

    For example, you can specify the following for a column:

    -- Move the data to temporary storage.
SELECT * INTO TemporaryStorage.dbo.Region_Storage FROM Region

-- Deploy column drop/add.

-- Move the data to the new column.
UPDATE Region SET ColumnNew = rs.ColumnOld FROM Region r JOIN TemporaryStorage.dbo.Region_Storage rs ON r.RegionID = rs.RegionID

  • Use bcp Utility, Data Transformation Services (SQL 2000), or SQL Server Integration Services (SSIS) (SQL 2005) to move the data automatically.

See Also

Tasks

How to: Control Data Loss during Deployment to an Existing Database

Concepts

Overview of Rename Refactoring

Other Resources

Renaming Database Objects
Deploying Refactoring Changes to a Database
Building and Deploying Version-controlled Databases