THE SQL Server Blog Spot on the Web
Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | Join | Help
in Search
Home Blogs Downloads   Opml View as RSS news feed in XML

Aaron Bertrand

Aaron is a senior consultant for SQL Sentry, Inc., makers of performance monitoring and event management software for SQL Server, Analysis Services, and Windows. He has been blogging here at sqlblog.com since 2006, focusing on manageability, performance, and new features; has been a Microsoft MVP since 1997; tweets as @AaronBertrand; and speaks frequently at user group meetings and SQL Saturday events.

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.

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.

 

Published Thursday, November 18, 2010 4:05 PM by AaronBertrand

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Geoff Hiten said:

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.

November 18, 2010 3:22 PM
 

AaronBertrand said:

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

November 18, 2010 3:25 PM
 

PercyReyes said:

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,

November 18, 2010 3:41 PM
 

Twitter Trackbacks for Aaron Bertrand : SQL Server v.Next (Denali) : Restoring a SQL Server 2000 database [sqlblog.com] on Topsy.com said:

PingBack from http://topsy.com/sqlblog.com/blogs/aaron_bertrand/archive/2010/11/18/sql-server-v-next-denali-restoring-a-sql-server-2000-database.aspx?utm_source=pingback&utm_campaign=L2

November 18, 2010 3:41 PM
 

Uri Dimant said:

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??

November 18, 2010 10:49 PM
 

AaronBertrand said:

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

November 18, 2010 11:02 PM
 

AaronBertrand said:

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.

November 18, 2010 11:09 PM
 

Uri Dimant said:

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

November 18, 2010 11:55 PM
 

Uri Dimant said:

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

November 18, 2010 11:55 PM
 

PercyReyes said:

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,

November 19, 2010 12:59 AM
 

WesleyB said:

@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

November 19, 2010 3:46 AM
 

AaronBertrand said:

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.

November 19, 2010 10:03 AM
 

PercyReyes said:

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)

November 19, 2010 10:37 AM
 

Aaron Bertrand said:

I see this question a lot: "I have a SQL Server 2008 R2 database. Can I attach it to a SQL Server 2008

January 21, 2011 6:45 PM

Leave a Comment

(required) 
(optional)
(required) 
Submit

About AaronBertrand

...about me...

This Blog

Syndication
©2006-2012 SQLblog.comTM
Brought to you by Adam Machanic & Peter DeBetta
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement