SQL Server 2008 / 2008R2 backup compatibility

Question

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.

Solution 1

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

Solution 2

I had original thoughts as well, a 0.5 version, a service pack with a different name.

Turns out I was wrong. 2008 R2 could very well be called 2010, but for marketing reasons it's not. Server 2003 R2 and Server 2008 R2 are completely separate operating systems from their non-R2 brethren.

So for the same reason you can't go from 2008 to 2005, you can't go from 2010 (aka 2008 R2) to 2008.

Comments

[Hafthor]

Version=660 for SQL v10.50.1092, Version=655 for SQL v10.0.2531 - this forward-only compat sucks - there should be a way to restore as a 655db and keep it that version

[Paul Randal]

Yup - that's it then. SQL Server is not future-version-compatible.

[Hafthor]

I don't need it to be future-version-compatible. I need to be able to make a 655 backup from R2.

[Hafthor]

Minor aside: Windows Server 2003 and 2003R2 are essentially the same OS. winsupersite.com/faq/win2003_r2.asp

[MrGigu]

Essentially the same in that Windows 95 and Windows 98 were the same OS as well. In 2003 and 2003 R2 there was no kernel change, but in 2008 to 2008 R2 there was a huge kernel change. Just because there wasn't in the past, doesn't mean there won't be in the future. It's all part of Microsoft's roadmap. I don't know if I like it, but that's just the way it is :(

[Paul Randal]

I know - but that's the problem - RTM/SP1 only supports 655, and R2 is at 660. It's not possible to backup to an earlier version - physical version upgrade steps are irrevocable.