4

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

Someone
  • 894
  • 3
  • 22
  • 43
Bogdan M.
  • 2,161
  • 6
  • 31
  • 53
  • Just use the new values for the accept answer for this question: http://stackoverflow.com/questions/7535/sql-server-2008-compatibility-with-sql-server-2005?rq=1 – Security Hound Jul 03 '13 at 14:04
  • You can use 2008 Compatibility level (under database properties, options tab). – Mansfield Jul 03 '13 at 14:05
  • wow, ty. need to test it but thank you for the hint – Bogdan M. Jul 03 '13 at 14:07
  • 3
    @Mansfield: the compatibility level only controls what features (like datatypes etc.) are available, but it does **NOT** make the `.mdf` or `.bak` files compatible. – marc_s Jul 03 '13 at 14:11

4 Answers4

14

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Just out of curiosity, if you try to do that, will sql server not let you? Or will it just cause problems down the line? – Mansfield Jul 03 '13 at 14:06
  • @Mansfield: you cannot do it - you **cannot** restore a 2012 `.bak` file (it will abort with an error message) - and you **cannot** attach a 2012 `*.mdf` to a SQL Server 2008 instance, either - again: you'll get an error. – marc_s Jul 03 '13 at 14:10
10

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.

Someone
  • 894
  • 3
  • 22
  • 43
2

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

Luis LL
  • 2,912
  • 2
  • 19
  • 21
-3

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

Surendra
  • 711
  • 5
  • 15
  • No - this does **NOT** work. This only limits the features - but it does ***NOT*** make the files compatible. – marc_s Jul 03 '13 at 14:16