[sql-server] SQL Server database backup restore on lower version

How to restore a higher version SQL Server database backup file onto a lower version SQL Server?

Using SQL Server 2008 R2 (10.50.1600), I made a backup file and now I want to restore it on my live server's SQL Server 2008 (10.00.1600).

When I tried to restore the backup onto SQL Server 2008 it gives an error i.e. Restore Failed because:

The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.1600.

How do I restore the backup file on this server?

You can try this.

1. Create a Database onto SQL Server 2008.
2. Using Import Data feature import data from SQL Server R2 (or any higher version).
3. use "RedGate SQLCompare" to synchronize script.

Go to Task->Generate Scripts...

In Advanced in "Types of data for script" select "Schema and data" and try to run this script in your lower version.

I appreciate this is an old post, but it may be useful for people to know that the Azure Migration Wizard (available on Codeplex - can't link to is as Codeplex is at the moment I'm typing this) will do this easily.

you can use BCP in and out for small tables.

BCP OUT command:-

BCP "SELECT *  FROM [Dinesh].[dbo].[Invoices]" QUERYOUT C:\av\Invoices1.txt -S MC0XENTC -T -c -r c:\error.csv

BCP IN command:- Create table structure for Invoicescopy1.

BCP [Dinesh].[dbo].[Invoicescopy1] IN C:\av\Invoices.txt -S MC0XENTC -T -c

You can not restore database (or attach) created in the upper version into lower version. The only way is to create a script for all objects and use the script to generate database.

select "Schema and Data" - if you want to Take both the things in to the Backup script file
select Schema Only - if only schema is needed.

Yes, now you have done with the Create Script with Schema and Data of the Database.

Here are my 2 cents on different options for completing this:

Third party tools: Probably the easiest way to get the job done is to create an empty database on lower version and then use third party tools to read the backup and synchronize new newly created database with the backup.

Red gate is one of the most popular but there are many others like ApexSQL Diff , ApexSQL Data Diff, Adept SQL, Idera …. All of these are premium tools but you can get the job done in trial mode ;)

Generating scripts: as others already mentioned you can always script structure and data using SSMS but you need to take into consideration the order of execution. By default object scripts are not ordered correctly and you’ll have to take care of the dependencies. This may be an issue if database is big and has a lot of objects.

Import and export wizard: This is not an ideal solution as it will not restore all objects but only data tables but you can consider it for quick and dirty fixes when it’s needed.

It's not pretty, but this is how I did it granted you have this option installed on your SQL 2008 R2 install..

1) Right click database in SQL Server 2008 R2 "Tasks".. "Generate scripts" in the wizard, select the entire database and objects in first step. On the "Set Scripting Options" step you should see a button "Advanced" , select this and make sure you select "Script for Server Version" = SQL Server 2008" not R2 version. This is a crucial step, because "import data" by itself does not bring along all the primary keys, constriants and any other objects like stored procedures."

2) Run the SQL script generated on the new install or database instance SQL Express or SQL Server 2008 using the query window or open saved .sql script and execute and you should see the new database.

3) Now right click on the new database and select "Tasks".. "Import Data.." choose source as the R2 database and the destination as the new database. "Copy data from one or more tables or views", select the top checkbox to select all tables and then next step, run the package and you should have everything on a older version. This should work for going back to a 2005 version as well. Hope this helps someone out.

You'd have to use the Import/Export wizards in SSMS to migrate everything

There is no "downgrade" possible using backup/restore or detach/attach. Therefore what you have to do is:

1. Backup the database from the server running the new SSMS/SQL version.
2. Import data from the generated .bak file, by expanding the "Tasks" menu(after right-clicking the target database) and selecting the "Import Data" option.

Another way to do this is to use "Copy Database" feature:

Find by right clicking the source database > "Tasks" > "Copy Database".

You can copy the database to a lower version of SQL Server Instance. This worked for me from a SQL Server 2008 R2 (SP1) - 10.50.2789.0 to Microsoft SQL Server 2008 (SP2) - 10.0.3798.0

Will not necessarily work

• Backup / Restore - will not work when the target is an earlier MS SQL version.

• Copy Database - will not work when the target is SQL Server Express: "The destination server cannot be a SQL Server 2005 or later Express instance."

• Data import - Will not copy the schema.

Will work

• Script generation - Tasks -> Generate Scripts. Make sure you set the desired target SQL Server version on the Set Scripting Options -> Advanced page. You can also choose there whether to copy schema, data, or both. Note that in the generated script, you may need to change the DATA folder for the mdf/ldf files if moving from non-express to express or vice versa.

• Microsoft SQL Server Database Publishing Services - comes with SQL Server 2005 and above, I think. Download the latest version from here. Prerequisites: sqlncli.msi/sqlncli_x64.msi/sqlncli_ia64.msi, SQLServer2005_XMO.msi/SQLServer2005_XMO_x64.msi/SQLServer2005_XMO_ia64.msi (download here).

You can use functionality called Export Data-Tier Application which generates .bacpac file consisting database schema and data.

On destination server, you can use Import Data-Tier Application option which creates and populates new database from pre-created .bacpac file

If you want just to transfer database schema, you can use Extract Data-Tier Application for creating file and Deploy Data-Tier Application for deploying created database schema.

I've tried this process on different versions of SQL Server from SQL 2014 to SQL 2012 and from SQL 2014 to SQL 2008R2 and worked well.

You can generate script from Task menu

For detailed reference

How to migrate a SQL Server database to a lower version