SQL Server 2008 R2 Express: MDF file not attached in SQL Server 2008 Developer

0
votes

I use this command on both express and developer addition

select @@VERSION

Express server result is below

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
    Apr 22 2011 11:57:00
    Copyright (c) Microsoft Corporation
    Express Edition with Advanced Services on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

and developer result is blow

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   
Jul  9 2008 14:43:34   Copyright (c) 1988-2008 Microsoft Corporation  Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

When I try to attach the MDF file from my Express edition to my developer edition, I get this error:

An exception occurred while executing a transact sql statement or batch the database 'example' cannot be opened because it is version 661. This server supports version 655. A downgrade path is not supported could not open new database 'example' create database is aborted. Microsoft Server, Error: 948

3
sql-serversql-server-2008-r2
Your question title says 2008 Express but you're actually running 2008 R2 Express - ta.speot.is
You cannot attach/restore a SQL Server 2008 R2 database back to a SQL Server 2008 server instance. What edition is irrelevant - the version is important; 2008 R2 cannot be restored/attached to 2008 - SQL Server is not backwards compatible in that way - never has been. - marc_s

3 Answers

3
votes

Since your version of Express is newer than your version of Developer, you will not be able to migrate directly, but you have a few options, in my order of preference:

  1. Upgrade to SQL Server 2008 R2 Developer Edition - by far the simplest approach
  2. Use a third party product to replicate schema and then the data (e.g. Red Gate SQL Compare / SQL Data Compare - I blogged about several other options here).
  3. Use a manual script, such as this one from Jonathan Kehayias
  4. Use the import-export wizard from Developer Edition to pull the data/schema from Express
1
votes

This is the problem of your sql version. You have created database in another version and the version in which you are trying to attach is different and old.

What you can do is create a backup file, and create new empty database and then restore that backup in it.

Note: While restoring, after providing backup file, see on left side, there is "options", click it, and then in there, you must select overwrite option, other wise it will give you structure error that the database backup that you are trying to attach has different structure than the database in which the backup is being restored.

0
votes

Since SQL Server does not allow downgrading you can create a backup of your database in SQL Server 2008 instance and use third party tools, ApexSQL Diff and ApexSQL Data Diff to restore the backup from an older to a newer version of SQL Server.

You can read detailed explanation of the process in this article:Restoring SQL Server database backup to an older version of SQL Server

Hope this helps

Disclaimer: I work for ApexSQL as a Support Engineer