Move database from SQL Server 2012 to 2008

Question

I have a database on a SQL Sever 2012 instance which I would like to copy to a 2008 server. The 2008 server cannot restore backups created by a 2012 server (I have tried).

I cannot find any options in 2012 to create a 2008 compatible backup. Am I missing something?

Is there an easy way to export the schema and data to a version-agnostic format which I can then import into 2008?

The database does not use any 2012 specific features. It contains tables, data and stored procedures.

Here is what I have tried so far

I tried Tasks → Generate Scripts on the 2012 server, and I was able to generate the schema (including stored procedures) as a SQL script. This didn't include any of the data, though.

After creating that schema on my 2008 machine, I was able to open the "Export Data" wizard on the 2012 machine, and after configuring the 2012 as source machine and the 2008 as target machine, I was presented with a list of tables which I could copy. I selected all my tables (300+), and clicked through the wizard. Unfortunately it spends ages generating its scripts, then fails with errors like "Failure inserting into the read-only column 'FOO_ID'".

I also tried the "Copy Database Wizard", which claimed to be able to copy "from 2000 or later to 2005 or later". It has two modes:

1. "Detach and Attach", which failed with error:

   Message: Index was outside the bounds of the array.
   StackTrace:    at Microsoft.SqlServer.Management.Smo.PropertyBag.SetValue(Int32 index, Object value)
   ...
   at Microsoft.SqlServer.Management.Smo.DataFile.get_FileName()
   

2. SQL Management Object Method which failed with error

   Cannot read property IsFileStream. This property is not available on SQL Server 7.0."

Answer 1

Right click on the database in the 2012 SQL Management Studio, and choose "Tasks -> Generate Scripts". Click past the welcome screen, choose "script entire database and all database objects". On the "specify how scripts should be saved" page, click "advanced". Under "General" in the pop up properties page, change "Types of data to script" from "Schema only" to "Schema and data", and change "Script for Server Version" from "2012" to "2008".

I then had to find some way to edit the start of this massive SQL file, to tweak how the database would be created -- see this q: http://stackoverflow.com/questions/102829/best-free-text-editor-supporting-more-than-4gb-files

And finally I had to find some way to run the SQL script, which was too large to open in SQL Management Studio -- see this q: http://stackoverflow.com/questions/431913/how-do-you-run-a-300mb-ms-sql-sql-file

Answer 2

I'm new to SQL, but I managed to migrate from 2012 to 2008. To do so I used the SQL import and export utility. I chose the SQL 2012 server and the database I want to migrate and finally the 2008 server and created a new database as my database of destination. It worked.

Answer 3

Try APEXSQL tools. They have a tool that will script the database and script the data also.

Answer 4

After creating that schema on my 2008 machine, I was able to open the "Export Data" wizard on the 2012 machine, and after configuring the 2012 as source machine and the 2008 as target machine, I was presented with a list of tables which I could copy. I selected all my tables (300+), and clicked through the wizard. Unfortunately it spends ages generating its scripts, then fails with errors like "Failure inserting into the read-only column 'FOO_ID'".

The problem is that it is not able to insert the ID columns, so:

1. Select all the tables by ticking them
2. Select all the tables (the way to do this is click on the first one and hold shift and click on the last table)
3. Click on "Edit Mappings" - you can see that just above the next button and next to preview .
4. You will get a window, tick on the "Enable identity insert" and then click next and go ahead, this should work. It worked for me.