VS 2010 Database Project dropping data on column name change

Question

Howdy,

I am testing out the new database project features of Visual Studio 2010 and want to change the name of a column in a table. I changed the name in the create script and deployed it against the database. The script that was generated just dropped the column and added a new column with the correct name, but all the data was lost.

Is there a setting that will not drop the column data?

I am looking for the "DataDude" solution to this issue. (If there is one)

PRINT N'Altering [dbo].[Users]...';

GO
ALTER TABLE [dbo].[Users] DROP COLUMN [TestX];

GO
ALTER TABLE [dbo].[Users]
    ADD [Testn] NVARCHAR (50) NULL;

GO

Thank you, Keith

Answer 1

If the table is small, you can create a new table with the correct columns, and then insert from the old table into the new one, changing columns as you go.

If the table is large, and you cannot afford either the time, RAM, or effort to copy the entire table, you can alter table add new_column then update table set new_column=old_column then alter table drop column old_column.

Syntax is of course simplified.

Answer 2

The way that Visual Studio Database Projects handle schema changes lends itself to this kind of confusion. It's very difficult for VS to tell if you simply renamed a column (and should preserve the data) or removed the column and added a different one. My company worked for quite a while on a database schema source control product that had all of these same issues.

In the end, I've come to believe that the best way to handle schema changes is that proposed by K. Scott Allen (referenced by Jeff Atwood). This series of articles should set you on a course towards a better database version control solution.

Answer 3

Use the Schema View by clicking View -> Database Schema View

Expand the tables and its columns.

Right click the column and click Refactor -> Rename...

Change the name in the New Name field with Preview Changes box checked.

Notice it changes not only the column name, but also the stored procedures that may be referencing that column.

Inside the Database project a refactorlog file is created that shows the name change.

When the new schema is deployed against the existing database, it appears that DataDude looks at the refactorlog file and the dbo._Refactorlog table to determine which refactors need to be processed against the database.

Here is the code it generated using this procedure the change a column name that was also referenced in a stored procedure:

EXECUTE sp_rename @objname = N'[dbo].[Users].[TestF]', @newname = N'TestG', @objtype = N'COLUMN';

GO
PRINT N'Altering [dbo].[ListUsers]...';

GO
ALTER PROCEDURE [dbo].[ListUsers]

AS
    SELECT [ID], [FirstName], [LastName], [TestG]
    FROM Users
RETURN 0
GO

Keith