Creating a SQL 2000 compatible database from a SQL 2005 database using SQL Compare

Category: How do I?
Date: 04 Mar 2009
Product: SQL Compare
Versions: 8

It is possible to convert objects from or migrate a SQL 2005 database to a SQL 2000 database using SQL Compare.

SQL Compare creates a synchronization script in a way that is compatible with the database being synchronized. For example, if the database resides on the SQL 2000 platform, the database scripts will be created in the SQL 2000 syntax.

There is a workaround to allow a SQL 2005 database to be synchronized with a SQL 2000 server. In order to create a synchronization script that will run on SQL Server 2000, first you will need a SQL Server 2000. Creating a new database on a SQL Server 2005 and setting the compatibility level to 80 will still result in a SQL Server 2005 script.

First, create a new database on the SQL Server 2000 instance using Enterprise Manager, SSMS or writing a CREATE DATABASE [databasename] query.
Launch SQL Compare and choose your SQL 2005 database as the source and specify the empty SQL 2000 database as the target.
SQL Compare will produce a script to CREATE all objects in dependency order, and use syntax that is compatible with SQL Server 2000. Where possible, new features such as CLR assemblies are filtered out because they are incompatible with SQL Server 2000. Sometimes this is not possible, for instance if a stored procedure relies on a CLR function. Since the function cannot be created, the stored procedure cannot be successfully scripted. Analyzing your databases for these conditions first is recommended.

In addition to creating a brand-new backwards-compatible database using this method, you may also create SQL Server 2000-compatible migration scripts for an existing SQL 2000 database using the same method.

=============================================================
Please note that in version 7.x and lower of SQL Compare, it was necessary to place the SQL Server 2000 database on the left-hand side of the project screen and re-compare the two databases if necessary. Version 8 will adapt the script syntax at the time that the synchronization wizard is run without the need to change the comparison settings and/or re-compare the databases.

Document ID: KB200804000233 Keywords: SQL,Compare,2000,2005,compatible,HowTo

Was this article helpful?

Search support