CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2012
Bogdan M.
Bogdan M.

Reputation: 2181

Create SQL Server 2012 database compatible for 2008

Is there a way to create a database with SQL Server 2012 and to be able to pass it to SQL Server 2008?

Upvotes: 4

Views: 13240

Answers (4)

Someone
Someone

Reputation: 904

I just transferred a database from 2012 to 2008 R2.

(this requires both to be online and you to have setup the new database with permission to access it, I did this in an office enviroment where the 2012 database was used to create a C# application but the database it was running on was for 2008, you could do this locally I suppose and then backup the data from 2008 to a .bak file once you did the below but I haven't tested that)

I exported the structure of the tables as SQL by;

-> Right clicking the database
-> Tasks
-> Generate scripts
-> Choose Objects (Script entire database and all database objects) 
-> Save to New Query Window (Click Advanced and under "Script for server version" choose your version) 
-> Click Next and Next and it should generate SQL to new window. 
-> I removed everything above "GO, ALTER DATABASE" 
-> Create the new database on the 2008 Server using the same name and run the above SQL to make the tables.

To do the table data:

-> In the 2012 database right click the database and click tasks
-> Export data
-> Choose the source
-> Next 
-> Choose the destination
-> Copy data from one or more tables
-> Next
-> Select the tables
-> Next
-> Run Immediately

Hopefully this works / helps someone else out as it took me a while to figure out.

Upvotes: 10

marc_s
marc_s

Reputation: 754538

As in backup your 2012 database and restore it to 2008?

NO, YOU CANNOT DO THAT

There's no way, no method, no trick, no hack, no workaround - it just CANNOT be done.

You need to use .sql scripts for structure and data to move from any newer SQL Server version an older version. SQL Server backup files (*.bak) and actual database files (*.mdf, *.ndf) have never been backwards compatible.

And to debunk a myth: setting the compatibility level only limits the available features (like datatypes etc.) - but it DOES NOT make the *.bak backup or *.mdf data files compatible with an older version.

Upvotes: 14

Luis LL
Luis LL

Reputation: 2993

In principle no. You can give compatibility model 100 (SQL 2008 / 2008R2), but you'll not be able to (backup/restore) nor (Detach/Attach) in from 2012 in a SQL2008 server.

if you only need to move schema, the only workaround that I know is to generate the schema and then create the database in the SQL2008. if you need to move the data, it will require much more work, and you can try some tools like SSMS tools | Generate Insert Statements

Upvotes: 2

Surendra
Surendra

Reputation: 721

actually there is, you can set the compatibility level of your database to 100.

More about this can be found at this link http://msdn.microsoft.com/en-us/library/bb510680.aspx

Upvotes: -3

Related Questions