Move database from SQL Server 2012 to 2008

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 TasksGenerate 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."

Rich

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: https://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: https://stackoverflow.com/questions/431913/how-do-you-run-a-300mb-ms-sql-sql-file

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]

編集
0

コメントを追加

0

関連記事

分類Dev

Export SQL Server 2014 database (2008 compatible) in a backup file compatible with 2012

分類Dev

Correct connection string MS sql server database 2008

分類Dev

Bulk insert into the SQL Server 2012 database - C#

分類Dev

How to search a column name in all tables in a database in SQL Server 2012?

分類Dev

SQL Server2008および2012にアクセスするPDODBLIB

分類Dev

エラー948:SQL Server 2008R2を使用するVisualStudio 2012

分類Dev

SQL Server 2012CTEをSQLServer2008に変換する

分類Dev

SQL Server 2008 Trigger

分類Dev

SQL Server 2008 SELECT

分類Dev

SQL Server 2008 - With 句

分類Dev

How to get COUNT(*) from one partition of a table in SQL Server 2012?

分類Dev

Subtract hours from SQL Server 2012 query result

分類Dev

Delete duplicate records from SQL Server 2012 table with identity

分類Dev

Exporting database from SQL Server to Azure

分類Dev

SQL Server2008を使用してVisualStudio2012で@@ identityを取得する方法

分類Dev

Pivot table in SQL Server 2008

分類Dev

Partitioned table - SQL Server 2008

分類Dev

Update to Openrowset SQL SERVER 2008

分類Dev

IBM WORKLIGHT SQL Server 2008

分類Dev

Numbering islands in SQL Server 2012

分類Dev

SQL Server 2012 Pivot Table

分類Dev

Restore SQL Server 2012 error

分類Dev

"Incorrect syntax near 'OFFSET'" modift sql comm 2012 to 2008

分類Dev

Upgrading sql management studio 2008 r2 to 2012

分類Dev

Upgrade from SQL Server 2008 to 2017 causes date subtraction error on Access front end form using DCount

分類Dev

SELECT query that Selects from Next Row given certain criteria using SQL Server 2008 R2?

分類Dev

MS SQL Server 2008R2 / 2012とJavascriptの間の日付の不一致を修正する方法

分類Dev

Laravel connect to a SQL Server 2008 named instance

分類Dev

Paging with Entity Framework 7 and SQL Server 2008

Related 関連記事

  1. 1

    Export SQL Server 2014 database (2008 compatible) in a backup file compatible with 2012

  2. 2

    Correct connection string MS sql server database 2008

  3. 3

    Bulk insert into the SQL Server 2012 database - C#

  4. 4

    How to search a column name in all tables in a database in SQL Server 2012?

  5. 5

    SQL Server2008および2012にアクセスするPDODBLIB

  6. 6

    エラー948:SQL Server 2008R2を使用するVisualStudio 2012

  7. 7

    SQL Server 2012CTEをSQLServer2008に変換する

  8. 8

    SQL Server 2008 Trigger

  9. 9

    SQL Server 2008 SELECT

  10. 10

    SQL Server 2008 - With 句

  11. 11

    How to get COUNT(*) from one partition of a table in SQL Server 2012?

  12. 12

    Subtract hours from SQL Server 2012 query result

  13. 13

    Delete duplicate records from SQL Server 2012 table with identity

  14. 14

    Exporting database from SQL Server to Azure

  15. 15

    SQL Server2008を使用してVisualStudio2012で@@ identityを取得する方法

  16. 16

    Pivot table in SQL Server 2008

  17. 17

    Partitioned table - SQL Server 2008

  18. 18

    Update to Openrowset SQL SERVER 2008

  19. 19

    IBM WORKLIGHT SQL Server 2008

  20. 20

    Numbering islands in SQL Server 2012

  21. 21

    SQL Server 2012 Pivot Table

  22. 22

    Restore SQL Server 2012 error

  23. 23

    "Incorrect syntax near 'OFFSET'" modift sql comm 2012 to 2008

  24. 24

    Upgrading sql management studio 2008 r2 to 2012

  25. 25

    Upgrade from SQL Server 2008 to 2017 causes date subtraction error on Access front end form using DCount

  26. 26

    SELECT query that Selects from Next Row given certain criteria using SQL Server 2008 R2?

  27. 27

    MS SQL Server 2008R2 / 2012とJavascriptの間の日付の不一致を修正する方法

  28. 28

    Laravel connect to a SQL Server 2008 named instance

  29. 29

    Paging with Entity Framework 7 and SQL Server 2008

ホットタグ

アーカイブ