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
3 Responses
This is true, there are several ways of doing a ‘downgrade’ with a third-party tool. ISQL Toolbelt is the one I’m most familiar with. One can use either SQL Compare/Data compare, or SQL Packager will do it, or you can script something to do it with the SDK. It is unusual to want to downgrade, and it is a very slow business on a large database, but it always makes the upgrade a more relaxing experience if you know you can either do a bareboned downdrade or resore an old backup and use Data Compare to update the backup from the ‘upgraded’ system.
>> nothing like the changes from SQL 6.5 to SQL <<
What are you talking about? The changes were *dramatic*!
In 6.5 dbs were assigned not to files but to devices which were taking up whole disk partitions (mostly). MSSQL7 persistence storage was a total rewrite of the sybase cruft.
cheers,
I was attempting to (and apparently poorly) show that there were a lot of changes from 8-9, but there were even more from 6.5-7.