I see this question a lot:

I have a SQL Server 2008 R2 database. Can I attach it to a SQL Server 2008 SP2 instance?

Part of this stems from the confusion over exactly what 2008 R2 is in the first place. It is not a service pack to SQL Server 2008; it is its own, full-fledged version. A lot of people don't fully realize this because of various reasons, including:

• they're on software assurance, so the upgrade to 2008 R2 was covered by their 2008 licenses, and they therefore didn't really realize it was a full version;
• they're not running 2008 R2 in production, but they do have it in development, and didn't notice the @@VERSION change; or,
• they're confused by the horrible naming choice.

But to answer the question: No, you cannot backup/restore or detach/attach a SQL Server 2008 R2 database to any previous version, nor can you log ship or mirror in that direction. That is also true in going from Denali to 2008 R2/2008/2005, or from 2008 to 2005, or anything from the last 6 years to 2000 – you just can't do it. A lot of people expect to be able to just set the compatibility level lower, however that is not the answer. It is the database version and the internal file/page structures that matter here; compatibility level just tells the parser and engine how to handle certain features.

What About Same Version, Lower Service Pack?

Well, I should have known this question would come up too. Let's say you are building your database in SQL Server 2008 SP2 and you are ready to deploy it to production, but production is still at SP1. You want to get the code to production but the cycle to introduce SP2 there is far too long. (Now, of course, and with the understanding that hardware equivalency is tougher, there should be a QA environment that matches production exactly in terms of software / configuration / version. So it shouldn't be going from dev to production in this manner, but I understand this is the reality for many people.)

That aside, can you do it? Yes. I set out to prove this. In a VM I laid down three instances of SQL Server 2008: one at RTM, one at SP1, and one at SP2. I created two databases in SP2: SP2DB, and SP2DB_15K. I created one database in SP1: SP1DB. I then took backups of SP1DB and SP2DB, and tried restoring them on the RTM instance. This worked fine and I was able to access the databases.

On the SP2 instance, I ran the following command, to enable 15K partitions for the SP2DB_15K database:

EXEC dbo.sp_db_increased_partitions 'SP2DB_15K', 'ON';
GO

Then I backed up the database and tried to restore it on the RTM instance. I received the following error:

If I tried to restore it on the SP1 instance, I received an almost identical error (predictably, the only thing that changes is the version string):

So, this exception aside, the answer is, yes, at least in SQL Server 2008, you can take a backup or detached database and restore it on an earlier service pack or RTM. Will this always be true? It's tough to say. Denali SP1 may introduce some change to the internal structure or database version, optional or otherwise, that will prevent it from being restored on RTM, just like our exception above.

Going Forward Always Works, Right?

As for the other direction, it is usually fine to backup/restore, detach/attach, log ship or mirror from an older version to a newer version; however, there are exceptions. For example, Denali does not support the 80 compatibility level, so restoring or attaching a SQL Server 2000 database on a Denali instance is out of the question (I blogged about this here in November). Another example is with SQL Server 2008 SP2 – if you have enabled support for 15,000 partitions, the internal database version is upgraded to 662, and you cannot then migrate to SQL Server 2008 R2 because R2 does not understand that version (it is expecting 661) – in this case you will have to wait until 2008 R2 SP1, when support for 15K partitions is added and the database versions will once again have an upgrade path. Here is what happens when I try to restore the SP2DB_15K database on an R2 instance:

(Note that this doesn't happen with the SP2DB database, which did not have 15K partitions enabled.)

It does work on Denali, however; here is what happens when I restore the SP2DB_15K database on a Denali CTP1 instance:

And the messaging is similar for the other databases from the 2008 instance, except their version starts at 661 instead of 662.

But I'm Still Stuck!

So, you have a database that has been upgraded to 2008 R2 and you need to get it back onto 2008 or 2005? There is no straight path for this scenario; it's kind of like driving in Boston. Assuming you aren't using any features that are exclusive to the newer version (and that you can suspend activity against the newer database temporarily), you can use a schema comparison tool like Red Gate's SQL Compare, to build the modules and schema in an empty database on the older version, then use Integration Services to migrate the data.