Incremental SQL Deploy: When a schema change might cause data in a table to be lost

Why does a SQL table change in a VS2010 DB Project raise the “The schema update is terminating because data loss might occur” deployment error?

This question has come up a lot lately…

Get acquainted with the “Block incremental deployment if data loss might occur”

SQL deployment property

1.       “When you deploy changes to an existing database, some changes could cause data loss. If a change might cause data in a table to be lost, the deployment will be canceled if the Block incremental deployment if data loss might occur check box is selected. By default, this check box is selected, but you can find it in the Properties window for your project. For more information, see An Overview of Database Project Settings.” [1]

Changes that can raise the “The schema update is terminating because data loss might occur.” error

1.      “The following kinds of changes will cause data loss: if a table is dropped and recreated, if you change the size of a column (char(100) to char(50) or nchar(100) to char(100)), or if you change the collation of a column that has a character type.“ [1]

2.       “…, if you have renamed your objects or columns at the source you might incur data loss while updating the target.” [2]

Avoiding the “The schema update is terminating because data loss might occur.” error

1.       Table Renaming: When you use refactoring to rename a database object or to move a database object to another schema, the refactoring log file records that action. At deployment time, the information in the log file helps preserve the intent of your changes. For example, you might lose data if you renamed a table, because the table would be dropped and a table would be created with the new name. With the refactoring log file, the deployment script can instead rename the table, preserving your intent and your data. [1]

Note: To perform an table renaming using database refactoring [In4mr]

1.       select the table in the solution explorer

2.       On the File Menu select Data à Refactor à Rename

3.       This will update all non-dynamic SQL references in the project as well. Dynamic SQL will have to be updated manually or via find and replace

2.        Script data before renaming columns: As long as your source has new objects there is nothing to worried about, but if you have renamed your objects or columns at the source you might incur data loss while updating the target. For example in the above schema comparison result image you can see the EmployeeID column has been renamed to BusinessEntityID, so while updating the target it will drop the EmployeeID column and add the BusinessEntityID column which will have no data even though the EmployeeID column has data in it. [2]

3.       Column Renaming: Column renaming can be done by refactoring as well, but the process id slightly different. [in4mr]

Note: To perform an table renaming using database refactoring

1.       At the top right corner of the Solution Explorer panel click the Schema View button.

Schema View (Far Right)

2.       Browse to the table containing the column

3.       Expand the columns node

4.       Right click on the column to be renamed

5.       Select Refactor à Rename

6.       This will update all non-dynamic SQL references in the project as well. Dynamic SQL will have to be updated manually or via find and replace

4.       Adding Columns: Adding a column to a table's existing column list will not cause data loss. [infmr]

References:

1.      An Overview of Database Build and Deployment

                        http://msdn.microsoft.com/en-us/library/aa833165.aspx  

                        MSDN , July 2010

2.       Schema Compare in VS 2010

                                http://www.mssqltips.com/tip.asp?tip=2089

                                Arshad Ali , 8/12/2010

I hope this is helpful,

In4mr

my@in4mr.com