Migrating SQL Server 2005 back to SQL Server 2000

Microsoft SQL Server 2000
SQL Server 2005
SQL Server migration
How would I go about migrating SQL Server 2005 back to SQL Server 2000?

Answer Wiki

Thanks. We'll let you know when a new response is added.

There is no easy way to go back from SQL 2005 to SQL 2000.

You will need to restore your most recent SQL 2000 backup, then manually move the data from the SQL 2005 server to the SQL 2000 server.

Discuss This Question: 4  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Apickel
    You can just re-install SQL Server 2000 on the same server if you have the space. It will run alongside a SQL Server 2005 installation. Once you have it re-installed, detach the databases from SQL Server 2005, BACKUP THE DATABASE FILES (.mdf, .ldf) and then attach them in SQL Server 2000. Of course you can only do this if the databases are set to compatibility level 80 (SQL Server 2000). If not then you will have run a database backup and then restore from that backup. Consult the documentation if you're not sure. Once you have the databases running on SQL Server 2000 just stop the SQL Server 2005 services and set them to manual.
    350 pointsBadges:
  • Denny Cherry
    It doesn't matter what compatibility level the database is set to you can not take data files from a SQL Server 2005 instance and attach them to a SQL Server 2000 instance. The SQL Server 2000 instance can not read the SQL Server 2005 data files. When the database is upgraded to SQL Server 2005 the meta data about the database tables is changed to be stored in the new SQL Server 2005 catalog store format instead of the SQL Server 2000 system tables. Not to mention the possibility of new columns created using new data types which were added in SQL Server 2005. SQL Server has always been able to upgrade the database files automatically (at least since version 7) but has never been able to downgrade the database files back to an older version.
    69,065 pointsBadges:
  • Apickel
    Ok MrDenny - squeeze my toes and call me a jelly donut you are right - it can't be done that way. Hopefully our friend looking for this advice doesn't have too many databases to move but fortunately there is a not so hard way to do it. The SQL Server 2005 Database Publishing Wizard will do the job fairly easily. Information can be found here:
  • edwinjhoffer
    Generate a full script for your database in SQL2005, and change the "Script for Server Version" option to SQL Server 2000. You can now recreate your database on the SQL 2000 server. After this is complete, use the export data feature to export from SQL 2005 to SQL 2000.
    3,740 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: