Comparing databases on different SQL Server versions
Published 04 June 2013
This topic provides additional information if you're comparing databases on different versions of Microsoft SQL Server.
Comparing a SQL Server 2005 or 2008 database with a SQL Server 2000 database
If you're 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're 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) can't decrypt objects that are encrypted in a SQL Server 2005 or SQL Server 2008 database.
If an encrypted object which can't 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 can't compare the encrypted objects, or display their creation scripts, and can't 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 can't compare the encrypted objects, or display their creation scripts, and can't 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.
