I want to start using SQL Server 2008R2, but I need to also be able to deploy my apps with databases to SQL Server 2008. I just found that I cannot backup 2008R2 to restore 2008. Why? What are my options?
I can kinda understand the forward only compatibility when we went from 2005 to 2008, although I still found it annoying, but I don't see why it had to happen on this .5 release.
Best Answer
There must be a bump in the database version. Do an sp_helpdb on the database and look in the status column. Part of it will list the version (the string will be 'Version=XXX'. What does it say?
If the version number is 655, that's SQL 2008 RTM/SP1. Anything higher means the version number was bumped for R2 and there's no way to restore on any previous version (including SQL 2008 RTM/SP1). The only way to get the data back to a previous version database is export/import.
Thanks
[Edit: I just blogged about this, with some links to deeper explanations of physical version number changes. See SQL Server 2008 R2 bumps the database version.]