Home > IT Answers > SQL Server > Migrating SQL Server 2005 back to SQL Server 2000
SQL Server Ask the Experts
2,585 pts.
 Migrating SQL Server 2005 back to SQL Server 2000
SQL Server 2000, SQL Server 2005, SQL Server migration
How would I go about migrating SQL Server 2005 back to SQL Server 2000?

Software/Hardware used:
ASKED: December 29, 2008  7:42 PM
UPDATED: December 31, 2008  7:39 PM
RELATED QUESTIONS
Answer Wiki:
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.
Last Wiki Answer Submitted:  December 30, 2008  3:08 am  by  Denny Cherry   64,520 pts.
All Answer Wiki Contributors:  Denny Cherry   64,520 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

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.

Apickel
 350 pts.
 

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.

Denny Cherry
 64,520 pts.
 

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:

Apickel
 350 pts.