CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2012
Andreu
Andreu

Reputation: 107

How do I convert a SQL Server 2008 .mdf file to SQL Server 2012?

I need to convert a file DATABASE.MDF from SQL Server 2008 to SQL Server 2012?

Database 'Sales' 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.
Could not open new database 'Sales'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 950)

Upvotes: 5

Views: 25347

Answers (2)

qianlong
qianlong

Reputation: 41

The OP seems to have been mistaken about his DB type. Version 539 of SQL Server is not 2008 but SQL Server 2000. See this thread: SQL Server file version number: where can I find a reference?

I've run into this same error message trying to attach/restore a SQL Server 2000 db to an instance of SQL Server 2012. Perhaps there was a way around it, but what I did instead was first convert to SQL Server 2008 R2, and then to SQL Server 2012. This executed with no trouble.

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280320

Just attach it and it will be upgraded. If you don't have the LDF file (and why don't you have the LDF file?) you may have to use:

CREATE DATABASE [DatabaseName] ON 
    (FILENAME = N'Drive:\path\file.mdf')
    FOR ATTACH_REBUILD_LOG;

There are some caveats, of course...

  • seeing as you only have the .mdf file, you may possibly be in for a rude awakening. If the file was not cleanly detached from SQL Server, it may not be re-attachable, and you will have to go back to the source server for a proper backup (not an .mdf file). This will all depend on where you got the .mdf file you're trying to upgrade. If your .mdf file was not cleanly detached, you may get this error:

File activation failure. The physical file name "..._log.LDF" may be incorrect.
The log cannot be rebuilt because the database was not cleanly shut down.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'DatabaseName'. CREATE DATABASE is aborted.

  • if your database is in 80 compatibility mode, it will be upgraded to 90. This may mean some working code may suddenly break, such as *=/=* join syntax.
  • if your database is > 10GB and you try attaching to Express (and maybe LocalDB, have never tried this), the attach will fail (or maybe the attach succeeds but will fail at the first autogrow? Have not tested this either).
  • you will also have issues if your database was created on 2008 Enterprise / Developer and you used features only available there, and you attach the database to Standard or Express editions...
  • OK, from your error message, you are not trying to attach a 2008 database at all, but rather a 2000 database. You cannot attach this directly to SQL Server 2012. You will first need to attach it to any instance of SQL Server 2005, 2008 or 2008 R2 to upgrade it to that version, and then back it up and restore it to SQL Server 2012.

FWIW, a much, much, much safer way of moving a database from one instance to another is using BACKUP/RESTORE (the reason is that if your backup fails, you still have a copy of the database - if you detach a database, and something goes wrong, you have zero copies). Though some of the issues above may still apply even with this safer approach.

Upvotes: 15

Related Questions