I can't attach my database to SQL Server 2008 Instance

Question

I can't attach my database to SQL Server 2008 Instance

MohamedDBA

SSC-Addicted

Points: 447
More actions
December 25, 2013 at 10:10 am

#281516

I was attaching database named 'Books' on SQL Server 2012 instance , and when I detach 'Books' database form SQL server 2012 Instance and trying to attach it to SQL Server 2008 instance this message shows up to me , what I can do please help

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55454 More actions · Answer 1

You cannot attach or restore a higher (newer) version database to a lower (older) version.

1 solution is to script the objects on 2012 server (in 2008 compatibility version), run the script on 2008 server, then export the data for all the tables from 2012 server to the 2008 server.

MohamedDBA SSC-Addicted Points: 447 More actions · Answer 2

But the compatibility level of the database is - SQL Server 2008 (100) , and this database was created on SQL Server 2008 before I attach It to SQL Server 2012 Instance. now I change my mind and I want to attach it again to SQL Server 2008 Instance for testing some things.

homebrew01 SSC Guru Points: 55454 More actions · Answer 3

Someone else will probably have a better explanation, but I believe that although it is running in 2008 compatibility mode, it is still a 2012 database underneath. It just can not make use of new 2012 features, and acts like a 2008 database.

Lowell SSC Guru Points: 323482 More actions · Answer 4

MohamedDBA (12/25/2013)
But the compatibility level of the database is - SQL Server 2008 (100) , and this database was created on SQL Server 2008 before I attach It to SQL Server 2012 Instance. now I change my mind and I want to attach it again to SQL Server 2008 Instance for testing some things.

Compatibility level only tells the SQL Engine that it is supposed to limit the syntax hitting the database to follow specific limitations; the compatibility level does not alter or change the way the database is actually stored and structured.

you can change the compatibility level back and forth, but SQL does not change the way it is physicially kept, it only varies permissible syntax from that point on.

a database , whether restored from a .bak or attached .mdf files, is modified to the version of the SQL server it was restored/attached on, and there is no going back.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Summer90 SSC-Dedicated Points: 32864 More actions · Answer 5

Yes, compatibility is simply that.... Once you attach or restore a db from a lower level into a higher level it is upgraded behind the scenes to the higher level of the version SQL Server.

The only way around that is to script out all of the objects and then use export/import wizard to move the data from SQL2012 db to SQL2008 db.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

MohamedDBA (12/25/2013)
But the compatibility level of the database is - SQL Server 2008 (100) , and this database was created on SQL Server 2008 before I attach It to SQL Server 2012 Instance. now I change my mind and I want to attach it again to SQL Server 2008 Instance for testing some things.

Compat mode just tells the query parser and query processor how to handle certain T-SQL constructs. A database that is attached to a SQL 2012 instance is a SQL 2012 database in every way and cannot be downgraded.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281324 More actions · Answer 7

MohamedDBA (12/25/2013)
But the compatibility level of the database is - SQL Server 2008 (100) , and this database was created on SQL Server 2008 before I attach It to SQL Server 2012 Instance. now I change my mind and I want to attach it again to SQL Server 2008 Instance for testing some things.

Do you have a backup of the database from before you attached it to the 2012 instance?

If not, you will need to script it out like the others have said.

Think of compatibility level as compatibility mode. You now have a 2012 database running in a different mode but it is indeed a 2012 database at this point.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

free_mascot One Orange Chip Points: 27168 More actions · Answer 8

Hi Mohamed,

If what you said is correct than we are able to detach the database by changing the compatibility and attached to the lower version. But this is not possible.

Refer following link for further detail which explains about compatibility:

http://technet.microsoft.com/en-us/library/bb510680.aspx

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."