SQL Server v.Next (Denali) : Restoring a SQL Server 2000 database

I guess the title should be, "You can't restore a SQL Server 2000 database on Denali."  I had the occasion just today to recover some data from a backup I took way back in 2008 (!) for a colleague who was still on 2000 at that time (again, !).  When I tried to restore the backup on my local Denali instance, I received this error message:

    Msg 3169, Level 16, State 1, Line 1
The database was backed up on a server running version 8.00.2040. That version is incompatible with this server, which is running version 11.00.1103. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
</div></td> </tr> </table>

The good news is, I also have a local instance of SQL Server 2008 R2, so I was able to restore, get the one piece of information I needed, and get on with my day.  I also discovered that I could restore on 2008 R2, back that database up, and restore on Denali.

So the moral of the story is, you can restore a 2000 backup on 2000, 2005, 2008, and 2008 R2, but not directly Denali.  I'm not complaining about this, as it's not something I expect to bite a lot of people, and there is still a path to get there (as long as the interim step is not attempting to restore to a lower SKU than required by the database).  I just didn't know that R2 could handle the database version upgrade path from 539 -> 661, and that Denali could handle the path from 661 -> 684, but Denali could not handle 539 -> 684.

 

Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at SQLPerformance and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a father of two, a huge hockey and football fan, and my pronouns are he/him. If I've helped you out, consider thanking me with a coffee. :-)

12 Responses

  1. PercyReyes says:

    Aaron, dont worry, I made a small change in my post to be much more explicit and does not generate doubts. SQL2000 won't be supported and SQL2000 compatibility option should be removed from the UI (in RTM version)

  2. AaronBertrand says:

    Percy, I thought you were suggesting that full 2000 compatibility will be fixed by RTM (sorry, not strong in Spanish).
    Uri, going from 2000 -> Denali is skipping three entire versions of the product.  Unless you have a very thorough test plan in mind, I think it can be quite risky – Denali's upgrade advisor isn't necessarily going to find every breaking change.  It can be tough to justify a 2008 or 2008 R2 license
    Wesley, exactly.

  3. WesleyB says:

    @Aaron: "I'm still having a hard time grasping the fact that R2 was an actual version"
    We've added unicode compression to R2 so that means the database version had to be changed

  4. PercyReyes says:

    Aaron,
    We all are working now on a CTP of Denali, so next releases it could change. On my post I just highlight that SQL2000 compatibility option was still on the UI, but this should be corrected later: D.
    I know that any version just can support 2 previous versions for compatibility, in this case(for denali) would be SQL2008 and SQL2005. Therefore, SQL2000 shouldn't suportted. I'm sure about it.
    for now, the only way to migrate from SQL2000 to Denali is through processes of backup / restore and attach the SQL2000 to SQL2008R2 and then to Denali.
    best wishes!
    regards,

  5. Uri Dimant says:

    Aaron
    Yes , I was thinking about installing Dev.Editon and doing restore over there. but that still does NOT sound good for me as I have some clients far away from me and not always possible to take over their servers, so  saying -"hey, go buy Dev Edition, install it,then do restore blabala and etc…."  frustrate me a little bit:-)
    BTW, why do you think that upgrading directly to Denali (when it will be released) is not the best approach? IIRW MS always recommends upgrading to the last version..

  6. Uri Dimant says:

    Aaron
    Yes , I was thinking about installing Dev.Editon and doing restore over there. but that still does NOT sound good for me as I have some clients far away from me and not always possible to take over their servers, so  saying -"hey, go buy Dev Edition, install it,then do restore blabala and etc…."  frustrate me a little bit:-)
    BTW, why do you think that upgrading directly to Denali (when it will be released) is not the best approach? IIRW MS always recommends upgrading to the last version..

  7. AaronBertrand says:

    When they're ready to upgrade, if they really want to go from 2000 -> Denali (probably not the best approach, IMHO), you may have to come up with something more creative.  For example, install Eval or Developer of 2008 or 2008 R2 somewhere, backup the 2000 database(s), restore on 2008 or 2008 R2, then back up that upgraded version, then finally restore it on Denali.  The only nice thing about this (unless you like downtime) is that you can test it over and over before upgrade day.
    I'm not sure you'd be able to use replication or log shipping from 2000 -> Denali to try to minimize downtime.  And I'm sorry but my 2000 knowledge is way behind me these days.

  8. AaronBertrand says:

    I think you might be out of luck Uri.  The installation center offers the following option:
    http://twitpic.com/382v9w
    Notice 2000 isn't listed.  I doubt this was an oversight.  I don't share the same enthusiasm as PercyReyes… just because 80 compat level is still offered in the UI, does not mean 2000 will continue to be fully supported (or that the option will remain there come RTM time).

  9. Uri Dimant says:

    Aaron
    I work with some clients (around 10) that are still on SQL Server 2000 and have no plan to move on with the next year or so… Does it mean when Denali will be released and they would like to upgrade (skipping 2005,2008,r2) it will not be possible? Sounds odd, does not it??

  10. PercyReyes says:

    Hi all!,
    I also had wrote about that problem on my blog some days ago… I Think this issue will be solved in RTM version of Denali:
    http://geeks.ms/blogs/ozonicco/archive/2010/11/16/184907.aspx
    regards,
    PercyReyes,

  11. AaronBertrand says:

    Yep, like I said, not disappointed with this, just initially surprised (I'm still having a hard time grasping the fact that R2 was an actual version).

  12. Geoff Hiten says:

    Aaron,
    This is pretty consistent with the "two versions back" support policy Microsoft has had since the current on-disk format was implemented for SQL 7.0. You get the same results on an attach or a restore.