Moving SQL Server 2005 Express databases to SQL Server 2000

Tuesday, February 10, 2009

Beyond general concept of upgrading, I faced a challenge of downgrade. I needed to migrate a database in SQL Server 2005 Express edition to SQL Server 2000 standard edition. I thought of how to do the same. Time permitted was just few hours.

 

I scratched on my head and thought of possible ways:

 1. Detach from 2005 and attach in SQL 2000: Can not be done. It is not allowed, even if you set the compatibility level of the database to SQL server 2000. This just sets the way how sql queries are parsed and processed.

 2. Import from SQL Server 2000 Enterprise Manager: Not possible, the Enterprise Manager does not connectthe SQL Server 2005 Express edition instance.

 3. Connect to SQL Server 2000 from SQL Server management Studio: But there is no option for export.

 4. I tried creating script. It creates the database, not the data.

 

So, started a google search. Got a good link for Moving SQL Server 2005 Express databases to SQL Server 2000 and read about using Microsoft SQL Server Database Publishing Wizard. I used the steps in the post:

  1. Ran the Database Publishing Wizard against my SQL Server 2005 Express database.
  2. Created a SQL Server 2000-compliant SQL script that contained all the SQL statements required to create the database. The SQL script also created all the INSERT statements required to populate the tables in the database.
  3. Created a new blank database in SQL Server 2000.
  4. Ran the SQL script from step 2 against the SQL Server 2000 database - using the Query Analyser.

But there were few issues. I tried running the script from the Enterprise Manager on the SQL Server 2000 instance. The Query Analyser did not allow me to open the script file, saying it has long lines. So I got worried. Then finally I could run it from SQL Server management Studio from which I connected to the 2000 server.

 

Happy downgrade!

 

 

 

 

Filed Under: ASP.NET, SQL Server

Comments (2) -

Rajib BorahIndiaRajib Borah said:

Gr8 job Smile
Google is alwayes there to help us

KangkanIndiaKangkan said:

Downgrading becomes the necessity at times!

Add comment

biuquote
Loading