SQL Compare - 10.2

SQL Compare

Comparing databases on different SQL Server versions - SQL Compare

This topic provides additional information for you if you are comparing databases that are on different versions of Microsoft® SQL Server™.

Comparing a SQL Server 2005 or 2008 database with a SQL Server 2000 database

If you are updating a SQL Server 2008 or SQL Server 2005 database to match a SQL Server 2000 database, you must not change the default project options on the Project Configuration dialog box.

However, if your database is on a SQL Server with case-sensitive sort order, you must select the Treat items as case sensitive project option.

If you are updating a SQL Server 2000 database to match a SQL Server 2005 database, note that:

  • SQL Compare may be unable to deploy all objects. Deployment warnings are displayed where possible.
  • SQL Compare version 7.0 (and earlier) cannot decrypt objects that are encrypted in a SQL Server 2005 or SQL Server 2008 database.

    If an encrypted object which cannot be decrypted exists in both databases, it is shown under the objects that exist in both but are different group in the upper (Results) pane. SQL Compare cannot compare the encrypted objects, or display their creation scripts, and cannot deploy them.

  • In SQL Compare version 7.1 (and later) you have the option to decrypt text objects in SQL Server 2008 and SQL Server 2005 databases that were created using the WITH ENCRYPTION option.

    Disabling this option can result in faster performance. To disable this option, on the Options tab of the Project Configuration dialog, clear the Decrypt encrypted objects on 2005 and 2008 databases check box.

    When this option is disabled, SQL Compare cannot compare the encrypted objects, or display their creation scripts, and cannot deploy them.

  • An extra line of white space may be appended to the creation scripts of stored procedures, functions, rules, and so on. So that SQL Compare does not flag these objects as different, you should select the project option Ignore white space.

When you create a default value or constraint in SQL Server 2005, the definitions of the default value or constraint are parsed by SQL Server 2005, and the parsed version is stored. The syntax of the SQL Server 2005 parsed version is not the same as the parsed version in SQL Server 2000.

For example, in SQL Server 2005, (1) is parsed to ((1)). SQL Compare highlights these differences in the lower (SQL Differences) pane, and if these are the only differences, it shows the objects to be identical.

For more information, see: Viewing the SQL differences.

Comparing SQL Server 2005 compatibility level 80 databases

If a SQL Server 2005 database has its compatibility level set to 80, it conforms to strict rules for views, stored procedures, functions, and DML triggers. Therefore, comparisons and deployments may fail.

Comparing SQL Server 2005 compatibility level 90 databases

If a SQL Server 2008 database has its compatibility level set to 90, it conforms to strict rules for views, stored procedures, functions, and DML triggers. Therefore, comparisons and deployments may fail.

See also

Setting project options

Deployment warnings

Was this article helpful?

Search support
Forums
Visit the SQL Compare forum.
Learning SQL Compare
Knowledge base

SQL Compare

all SQL products

all products

Notes & articles

SQL Compare

all SQL products

all products

Demos & walkthroughs

SQL Compare