Comparing databases on different SQL Server versions - SQL Data Compare

If you are comparing databases that are on different versions of Microsoft® SQL Server™, you may encounter problems with some data types.

CLR data types

You can update CLR data in a SQL Server 2008 or SQL Server 2005 database with values from a text or string data type in a SQL Server 2000 database. Ensure that the project option Transport CLR data types as binary is not selected.

SQL Data Compare considers the collation for string data. Therefore, if the collation is not the same, differences are reported.

Note that if you are comparing backup files, SQL Data Compare can compare CLR data types only as binary values.

XML data types

You can update XML data in a SQL Server 2008 or SQL Server 2005 database with values from a text or string data type in a SQL Server 2000 database. SQL Data Compare will attempt to preserve white space. SQL Data Compare supports DTD (Document Type Definition), except for default attributes and entities.

Some data, such as XML encoding and DTD, cannot be stored in the SQL Server 2008 or SQL Server 2005 representation. Therefore, if you convert data from a string data type to an XML data type, and then you convert back to a string data type, this information will be lost.

SQL Data Compare considers the collation for string data. Therefore, if the collation is not the same, differences are reported.

Note that you cannot use an XML column as the comparison key.

Was this article helpful?

Search support