How to attach database in SQL Server 2008 which was created in SQL Server 2012

0
votes

I have two database files named as .mdf and .ldf which are created in SQL Server 2012 and now on my pc I installed Visual Studio 2010 and SQL Server 2008 Express.

When I try to attach these database files to SQL Server 2008 it gives me error.

The database 'C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL10.SQLEXPRESS\MSSQL\DATA\CMS_DB.MDF' cannot be opened because it is version 706. This server supports version 655 and earlier. A downgrade path is not supported. Could not open new database 'C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL10.SQLEXPRESS\MSSQL\DATA\CMS_DB.MDF'. CREATE DATABASE is aborted.
An attempt to attach an auto-named database for file C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\CMD_DB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

When open database in ssms 2008 then error :

Attach database failed for Server 'MATRIX-PC\SQLEXPRESS'. (Microsoft.SqlServer.Smo)
Additional information:
An exception occured while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The database 'C:\USERS\RAJ\DESKTOP\COLLEGE MANAGEMENT SYSTEM(.NET 4.5)\ COLLEGE MANAGEMENT SYSTEM\BIN\DEBUG\CMD_DB.MDF' cannot be opened because it is version 706.
This server supports version 655 and earlier. A downgrade path is not supported.
Could not open new database 'C:\USERS\RAJ\DESKTOP\COLLEGE MANAGEMENT SYSTEM(.NET 4.5)\COLLEGE MANAGEMENT SYSTEM\BIN\DEBUG\CMD_DB.MDF'. CREATE DATABASE is aborted.
(Microsoft SQL Server, Error: 948)

2
sql-server-2008
You CANNOT do this - you CANNOT attach/detach or backup/restore from a newer version of SQL Server down to an older version. Just cannot be done. - marc_s
Really, there is no way to solve this problem. If any way then guide me. - argciv
The solution is to use the same version of SQL Server on all your platforms; why don't you just download and install SQL Server 2012 Express on your local PC? - marc_s
ok. Thanks. I will download SQL Server 2012 Express.. - argciv
you might have problems with the express edition if you are using some features of higher editions ... sqlmag.com/blog/… - Daniel Kienböck

2 Answers

0
votes

No, you can only move forward.

This means, you can restore the database from 2005 or 2008 to 2012 version but can not do it in reverse order. The next most popular question I receive is if we can’t restore a SQL Server 2012 database to earlier version what is the next best option?

I have personally faced this issue once before and I had manually created T-SQL script using Script and Data Generator Wizard in SQL Server 2012 and rebuild my database in SQL Server 2008R2. If you have many different databases which you want to address, you can use SSIS to automate the script generation.

0
votes

As noted if you want to access a 2012 db from 2008 you will have to migrate the database back to 2008. This will only be possible if you have not used 2012-specific features in the db. If you have and still want to perform the migration you should first try and identify these where they occur and fix those first.

As noted above one option is to generate T-SQL scripts and use these to regenerate the db in 2008. However for a large db this could be time consuming and hard work to get to actually work.

A better option than using the SSMS scripting wizard is to use a similar tool available on Codeplex called SQL Database Migration Wizard - http://sqlazuremw.codeplex.com/releases/view/32334. You want the latest version v4.x to work with SQL Server 2012.

The tool is originally intended to be used for migrating databases between SQL Server and Azure. However the tool works just as well between SQL Server 2012 and SQL Server 2008. The trick is to set SQL Server rather than Azure as the target in the advanced options.

The reason this is a better option than the SSMS scripting wizard is that it uses BCP for the data transfer rather than TSQL and so is much more efficient. I migrated a 7GB database in about 2 hours with this with most of the time spent drinking tea while the machine worked away. Importantly, it all just worked with no requirement for me to start editing files to fix problems like too many commands, timeouts etc.