SQL 2012 will not convert a SQL 2000 database?

Question

I have an existing database originally created in SQL 2000.  When attempting to attach it to SQL 2012 for the first time, I get...

Database '<name here>' cannot be upgraded because its non-release version (539) is not supported by this version of SQL Server. You cannot open a database that is incompatible with this version of sqlservr.exe. You must re-create the database.

However, I can attach it just fine to SQL 2005, 2008, or 2008 R2 with no issue.  And in fact if I attach it to any of those instances, then attach it to 2012, it works just fine.  But there appears to be no way to go directly from 2000 -> 2012.

Is this a bug?  Intentional?  I couldn't find any documentation about it's intent.  Suggesting that I recreate the database is a bit silly, there's a ton of tables and data inside it.  I have a large number of customers still on SQL 2000, and I'd like them to upgrade.  They're not going to readily have available other editions for an interim attachment, and there's too much data for me to download every data to my site, attach it once, and send it back.  

Is there another conversion tool that can be run?

Answer 1

I think, you can only attach databases from two version below, ie from 2005->2008/R2->2012, not 2000 to 2012.  You cannot attach a 2000 database to 2012. Although i haven't seen documentation on this, can you attach it to 2008/R2 then attach it to 2012 instance?

Thanks

Answer 2

only the last 2 (3) version can be restored to a SQL Server of a specific version.

For SQL 2012 this means that you can only attach / upgrade a database from SQL 2005 or newer.

In your case, attach the database to any of your lower edition (2005, 2008, 2008R2), upgrade the database level (database property) to latest version, detach it and attach it finally to your SQL Server 2012.

Answer 3

@SQL_Jay,

You're wrong. My friend has attached the Northwind database by way of his 11th version of SQL Server Management Studio. There is a solution and I hasn't solved it for a few days.

Answer 4

The problem is that SQL Server 2012 doesn't offer the "compatibility level" for MS SQL 2000 (8.0). Hence you need a MS SQL 2005 or 2008 instance (either install SQL 2005 Express instance or connect to some instance) and restore on that instance. Important! When you have the DB restored on 2005 (or 2008), go to its Properties -> Options and change "Compatibility level" to at least 2005. Then you can make a backup and restore it on 2012.

BTW. Same thing applied to MS SQL 7.0 databases which can only be restored - if I recall correctly - up to and including SQL 2005.