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

AaronBertrand — Thu, 18 Nov 2010 20:05:00 GMT

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.

Search results matching tags 'denali', 'SQL 11', 'backup', and 'SQL Server 2011'

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