Move database from SQL Server 2012 to 2008
- by Rich
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."
