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, 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)
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.
@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
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,
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..
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..
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.
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).
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??
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,
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).
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.