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

  • Are you able to export direct? I don't know how big your DB is, but this is what I would do when working with smaller databases.
    – Dave
    Aug 31, 2012 at 9:52
  • 2
    No older version of SQL Server can restore backups from newer versions. This is by design. Use the Export feature, script out the tables and the data, and set the script target to SQL 2008.
    – user3463
    Aug 31, 2012 at 15:29
  • 4
    "Trying to figure out why anyone in their right mind would migrate backwards." -- I have a production server running 2012, but my dev machine has only 2008. I'd like a local copy of the DB to aid development, but I don't want to install 2012 on my dev machine as I haven't that much free space left, and don't want to spend hours installing it. (So I'm spending hours trying to migrate backwards instead.... but that's just me.)
    – Rich
    Aug 31, 2012 at 17:11
  • 1
    You are almost there. The Export section you went into has an option to include data as well as the schema.
    – user3463
    Sep 2, 2012 at 2:56
  • 1
    Just wanted to add for Ramhound: I for one upgraded SQL 2008 R2 to SQL 2012 on my Dev environment and then, after I changed a lot of schema and data in my upgraded instance, realized VS 2010 cannot compare schema or data with SQL 2012. SSDT for VS 2010 and VS 2012 can compare schema with SQL 2012 but are not compatible with the original VS 2010 database projects, and cannot compare data. Long story short, I'd rather go back to SQL 2008 R2 and wait until a decision is made company-wise to move forward to 2012. But I'd like to save my new schema/data. AFAIK I'm in my right mind.
    – GFK
    Nov 13, 2012 at 13:49

7 Answers 7


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

  • what did you tweak at the start of the file?
    – JonoRR
    Oct 15, 2013 at 15:11
  • 1
    I can't remember exactly, sorry. App specific stuff, I think. Perhaps the database name or something like that. I think I would have added it here if it were relevant to people with the same problem.
    – Rich
    Oct 15, 2013 at 16:24
  • 3
    @JonoRR You would most likely want to edit the CREATE DATABASE statement and set the datafile paths to whatever makes sense on the destination server. Mar 20, 2014 at 22:54

For SQL migrations, use the free and open source SQL Database Migration Wizard.

I had a 5GB database with couple ~10 million records and tried the route via Generate Script and than ran it with sqlcmd.exe. First of all, the generated script was not always working correct. Secondly, sqlcmd.exe can fail on large files too, complaining about available memory. osql.exe works, but just takes ages (and has the same command line arguments).

Then I came across a wonderful tool for migrating SQL Server to SQL Azure databases. This works for SQL Server to SQL Server as well, for example if you'd like to migrate a SQL 2012 database to 2008 R2. It uses bcp.exe, which uses bulk copy. There is a GUI and command-line (Batch) version available and it's open source. See http://sqlazuremw.codeplex.com/. In my case, the operation took 16 minutes.

In an advanced screen you can select that your target is SQL Server, not SQL Azure.

  • 1
    Where do you set the 2008 R2 in this wizard?
    – JonoRR
    Oct 15, 2013 at 14:42
  • Awesome tip, I have restored a database from Express 2012 to Web 2008. Very easy.
    – rkawano
    Jul 25, 2014 at 18:43
  • Can confirm it works as advertised. Did a 2014->2012, cross domain, local SQL-servers. How has this tool avoided my searches for so long! Amazing even for non-azure use.
    – Gomibushi
    Jan 25, 2016 at 13:05

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.


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

  • "from $299" -- is there a free alternative?
    – Rich
    Sep 1, 2012 at 6:54

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.

I followed Rich's solution https://superuser.com/a/469597/312310 above and it worked great! Thanks - I can't vote up yet in here!

The extra steps I had to do to get this to work are:

  • Change the logical file names and ensure the path is correct. I also had to change the generated log name, else it tried to overwrite the mdf file with the log and then threw this error https://stackoverflow.com/questions/7534664/sql-server-script-error-database-is-already-in-use

    CREATE DATABASE [xxxxx] ON PRIMARY ( NAME = xxxxx', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\xxxxx.mdf' , SIZE = 14400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'xxxxxldf', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\xxxxx_log.ldf' , SIZE = 18560KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO

  • I had to comment out the generated section and add the user permissions manually afterwards

    USE [xxxxx]
    /****** Object:  User [xxxxx]    Script Date: 30/11/2014 12:44:07 ******/
    sys.sp_addrolemember @rolename = N'db_owner', @membername = N'Umbraco'
    sys.sp_addrolemember @rolename = N'db_datareader', @membername = N'Umbraco'
    sys.sp_addrolemember @rolename = N'db_datawriter', @membername = N'Umbraco'

Ran it up, and lo and behold I can now restore my new Umbraco 7.1.2 database to my web server that only has SQL Server 2008 R2!


Have a look at xSQL Schema Compare and xSQL Data Compare. You can first synchronize the schema with schema compare and then copy the data.

Disclaimer: I'm affiliated with xSQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.