Sql-server – Use Older Versions Of SQL Server Databases With 2012

sql server

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?

Best Answer

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

Related Solutions

Sql-server – Convert SQL Server 2012 SSIS package to 2008

Yes, you'll need to recreate the package in 2008. 2005/2008 packages can be upgraded to 2012, but downgrading doesn't work.

There is a codeplex project/utility that can move 2008 packages to 2005 by changing version numbers, and making small changes to the XML. Unfortunately this approach doesn't work with 2012, as the package format went through some big changes.

Sql-server – How to attach an .mdf file that was not properly detached

It sounds like the database is corrupt. Before you do anything further, make a copy of the files, and only work with copies.

You might try the following, in case the problem is isolated to the log:

CREATE DATABASE [GRMStemp]
  ON (FILENAME = 'c:\wherever\MDF_File.mdf')
  FOR ATTACH_REBUILD_LOG;

If that fails, there is a forum post on MSDN by Paul Randal which describes a process that might work. Basically:

on your 2008 instance, create a database with a data file of the exact same size (I wouldn't bother trying to put a 2000 instance anywhere)
shutdown SQL Server
rename the new database's mdf file, and paste the old one in its place
bring up the server and let the database attempt to be recovered and then go into suspect mode
put the database into emergency mode with ALTER DATABASE GRMStemp SET EMERGENCY;
run DBCC CHECKDB (GRMStemp, REPAIR_ALLOW_DATA_LOSS); which will rebuild the log and run full repair

BTW If the client has no idea what was in the database, and wasn't responsible enough to maintain proper backups, how important could the data be?

Best Answer

Related Solutions

Sql-server – Convert SQL Server 2012 SSIS package to 2008

Sql-server – How to attach an .mdf file that was not properly detached

Related Question