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.
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.
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: