Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

I can't attach my database to SQL Server 2008 Instance Expand / Collapse
Author
Message
Posted Wednesday, December 25, 2013 10:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 4:16 AM
Points: 29, Visits: 216
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

Post #1525904
Posted Wednesday, December 25, 2013 10:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:04 AM
Points: 2,795, Visits: 8,298
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.



Post #1525905
Posted Wednesday, December 25, 2013 10:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 4:16 AM
Points: 29, Visits: 216
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.
Post #1525907
Posted Wednesday, December 25, 2013 6:55 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:04 AM
Points: 2,795, Visits: 8,298
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.


Post #1525921
Posted Thursday, December 26, 2013 5:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:34 PM
Points: 12,755, Visits: 31,122
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1525954
Posted Thursday, December 26, 2013 10:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 21, 2014 10:38 AM
Points: 1,202, Visits: 2,663
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.



Post #1526005
Posted Thursday, December 26, 2013 1:59 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 41,570, Visits: 34,495
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 2008, MVP
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

Post #1526046
Posted Thursday, December 26, 2013 2:07 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:39 PM
Points: 20,485, Visits: 14,143
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1526049
Posted Tuesday, December 31, 2013 3:14 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:37 AM
Points: 2,403, Visits: 1,863
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."
Post #1526706
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse