Home / server / Questions / 58039
Accepted
Hafthor
Asked: 2009-08-25 08:42:12 +0800 CST

SQL Server 2008 / 2008R2 backup compatibility

  • 772

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.

sql-server

2 Answers

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

    • 4

  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.

    • 1