SQL Upgrades are a one way street.

That’s right folks.  Once you upgrade SQL Server versions there is no going back.

This means that SQL 2008 R2 databases can not be detached from a SQL 2008 R2 instance and attached to a SQL 2008 instance.  You can’t backup the SQL 2008 R2 database and restore is to a SQL 2008 instance.

You can’t take a SQL 2008 database and move it to a SQL 2005 instance.  Same goes from SQL 2005 to SQL 2000, or from SQL 2000 to SQL 7. No, you can’t put the SQL 2008 database into the SQL 2005 compatibility mode (aka 100) and then do it.  It still won’t work.

You can move up from SQL 2000, to SQL 2005.  Or from SQL 2005 to SQL 2008.  If you are real brave you can move from SQL 2000 directly to SQL 2008.  But these are one way operations.

When SQL Server moved from one version to another the way that the headers in the database file are written are changed.  Especially from SQL 2000 to SQL 2005, there were a lot of changes (nothing like the changes from SQL 6.5 to SQL 7).  There were less from SQL 2005 to SQL 2008, and less again from SQL 2008 to SQL 2008 R2 (so far).

If you need to go back for some reason, your only option is to script out all the objects, and create them on the older version.  You can then use BCP, DTS, SSIS, or scripting out the data to move the data from the higher version instance to the lower version instance.

Denny