Considerations for Preserving Data during Deployment and Schema Updates
Before you synchronize two schemas, you should consider configuring the settings of the database project to minimize the chance that your changes will cause the loss of valuable data. By configuring those settings, you can block the synchronization if it contains changes that are likely to remove data from the target database. For more information, see How to: Control Data Loss during Deployment to an Existing Database and How to: Set Options for Comparing Database Schemas.
However, this setting can cause results that you might not expect. In some situations, it prevents changes from being deployed even though no valuable data would be lost. In other cases, the setting allows changes to be deployed even though SQL Server could cause data loss when the target is updated.
Deployment Blocked Unnecessarily
If you configure the project settings to help prevent data loss, you might not be able to synchronize two schemas even though no valuable data would be lost. For example, you can delete a column of data in a database project and try to deploy that change to the target database. Even though you deleted the column intentionally, deployment will be blocked to prevent loss of data on the target.
As another example, deployment will be blocked if a data type in the source cannot be verified as compatible with the equivalent data type in the target. This situation can occur if either the source or the target contains a user-defined data type or a common language runtime (CLR) type. Deployment will be blocked if, for example, a data type in the source is defined as char(100) and the data type in the target is defined as a user-defined data type such as: CREATE TYPE [schema].[UserDefinedDataType] FROM char(100) NOT NULL.
If necessary, you can temporarily configure your project settings to allow synchronization after you inspect the deployment or update script and verify that the changes will not cause the loss of valuable data.
Data Loss during Unblocked Deployment
Even if you configure your database project to help prevent data loss, SQL Server can, in some cases, cause data loss during its execution of the synchronization. Before you synchronize two schemas, you should inspect the deployment or update script if it might contain the following:
Changes to the column order of a table.
Changes to a column data type to one that is not compatible with an existing data type and that uses SQL Native data types. This situation can occur if a data type change leads to data truncation, such as int to bit or nvarchar(100) to char(10).
Changes to a column property, such as the identity of a column.
Changes to properties of an identity column on a table, such as the identity seed.
Changes to a column that does not allow NULL values and that has no associated default value, such as changing a column to allow null values.
Changes to a column that does not allow NULL values when the table contains additional columns that are bound to the column that you are changing.
Addition of a column on which you have selected the Force table column order to be identical check box. For more information, see Options (Database Tools/Schema Compare).
Changes to the filegroup of a table.
If the deployment or update script contains these types of changes, you can modify it manually to preserve the data.
See Also
Tasks
How to: Control Data Loss during Deployment to an Existing Database
How to: Deploy Changes to New or Existing Databases
How to: Synchronize Database Schemas
Concepts
Terminology Overview of Team Edition for Database Professionals