SQL Compare 10

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

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

SQL Compare creates a deployment script in a way that is compatible with the database being deployed. 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 deployed with a SQL 2000 server. In order to create a deployment 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 can't be created, the stored procedure can't 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.


Didn't find what you were looking for?