Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite
1

We have been asked to move from SQL Server 2008 R2 over to 2012, the installation completed a few hours ago. Ever since I've been trying to attach databases from 2008 over to 2012.

It seems to be having no effect. Error messages below:

Attach database failed for Server 'Development\SQLSERVER'. (Microsoft.SqlServer.Smo)
Database 'PS_EmpData' 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.

I mean, this table is quite a big database; and the time it would take to re-create with all the inserts would be unreal. We are still operating with our SQL 2008 R2 for production, but development process is underway on 2012.

My overall question is, do we have to re-create the database to use 2012 or is there a work around?

share|improve this question
3  
Take a backup from the 2008R2 instance, and restore it to the 2012 instance. – Bridge Jan 23 at 16:29
What compatibility mode this database is set to? – Bridge Jan 23 at 16:33
@Bridge compatibility mode and database version are not the same thing. A database with a version from 2008 or 2008 R2, and a compatibility mode of 80 (2000), will attach / restore just fine, but it blindly upgrades the compatibility level to 90 (the lowest supported). – Aaron Bertrand Jan 23 at 17:21
I suspect that what is being attached to 2012 is not something that had already successfully been attached to 2008 / 2008 R2 (otherwise the version upgrade wouldn't have happened, and this database would no longer be stuck at 539). – Aaron Bertrand Jan 23 at 17:21
Also note that the error message is a bit Chicken Little. It is highly unlikely that you will have to re-create the database, we just need to find out more specific details about this database before we can tell you what the workaround should be. (Your definition of "quite a big database" would be useful, too.) – Aaron Bertrand Jan 23 at 17:22
show 1 more comment

migrated from stackoverflow.com Jan 23 at 17:09

4 Answers

up vote 6 down vote

The version (539) indicated in the error message says that this database is a Sql Server 2000 database and thus is not supported in Sql Server 2012 See here

Probably in your SqlServer 2008R2 this particular database has a compatibilty level set to Sql Server 2000. You could try to change this level going to the properties of the database, Options page and changing the compatibility level to 2008, then retry to attach the database to Sql Server 2012

share|improve this answer
2  
I also blogged about this here: sqlblog.com/blogs/aaron_bertrand/archive/2010/11/18/… – Aaron Bertrand Jan 23 at 17:13
@AaronBertrand sorry, if I had seen your article a few months ago I would have saved quite a headache – Steve Jan 23 at 18:47
SO just change the database compatibility options? I will inform you of the progress later on today when we get a chance to sit back down on the dev system – user1968541 Jan 24 at 13:50
up vote 3 down vote

Things don't add up. Either the database is from SQL 2008, and then it would be version 655 (or 661 if is SQL Server 2008 R2), or the database is version 539 which is basically an antediluvian version. I would much rather believe the database version field is actually corrupted in the file (eg. it got changed in the file copy). Make sure the file is identical with the source file from your SQL 2008 instance.

share|improve this answer
I kind of agree. If the 2000 database was attached / restored to the 2008 / 2008 R2 instance, its version should no longer be 539 (even if it is still in 80 / 2000 compatibility mode). I wonder if the source backup or mdf file from 2000, that was used to create the database on 2008 / 2008 R2, was also used to create the database on 2012 (as opposed to a backup or detached copy of the mdf file from 2008 / 2008 R2). It is also curious that it is an attach operation as opposed to a restore operation... – Aaron Bertrand Jan 23 at 17:18
1  
Oh, and +1 for antediluvian... – Aaron Bertrand Jan 23 at 19:14
up vote 2 down vote

The 539 internal database version is from SQL 2000. 2012 likely doesn't support directly coming from that version - but, since you have a 2008 R2 server, you probably have some options for upgrading that db on that server first.

If you have enough space on the 2008 R2 server for another copy of the db, get one on there, then try setting the compatability level to 100 (2008 R2), then detach and re-attach. The upgrade of the internal database version generally happens during the attach (or a restore).

I happen to have 2000/2008R2/2012 all deployed, so I'm going to do a quick test and ensure the above works.

Edit: Taking a backup from my 2000 server and restoring to the 2008 R2 server automatically upgraded the internal database version to 661 (2008 R2), though the compatability level is set to 80 (SQL 2000). I'm not sure how you ended up with a v539 db on 2008 R2, but try the attach/detach on a copy of it after modifying the compatability level, and check the internal version using (from Jonathan Kehayias blog post here):

DBCC TRACEON (3604);  
GO 
DBCC PAGE (ExampleDB, 1, 9, 3); 
GO 
DBCC TRACEOFF (3604);

Somewhere in the output should be an indicator of the internal version number:

dbi_version = 655
share|improve this answer
up vote 0 down vote

The error is stating that your 2008R2 was a non-release version. Is it a beta release or something? I have attached many 2008R2 DB's to 2012. Have you tried to do a backup in 2008 and restore in 2012? That also works for me.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.