SQL Server database backup restore on lower version

Question

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?

Hmm.. i am not sure if that would be possible (from higher version to lower version) .. other option you have is to export "data" (in excel etc) from new version and import it to old version. — Ankur, May 31 '11 at 04:58
You cannot do this - it's just not possible. SQL Server doesn't allow *downgrading" a database from a higher version to a lower one. — marc_s, May 31 '11 at 05:06
Microsoft knows everything about SQL server (since they created it) and yet moving data between 2 versions is impossible. I simply don't understand why some sort of compatibility mode not available in export. How hard it can be? — dvdmn, Mar 12 '15 at 14:15
@dvdmn as hard as any Top Gear episode where they say that - different types, different statements, different block layouts. Moving data between databases is *very* easy, no matter the versions involved. Downgrading though is *not*. — Panagiotis Kanavos, Apr 13 '16 at 14:04
@PanagiotisKanavos I disagree, in mysql you can export db as sql commands and you can restore it on any version of mysql (down or up). Yes you can export sql db as script too but restoring it not easy if you have some stored function/procedures that depends on each other. It is basically not useful/solution. — dvdmn, Apr 14 '16 at 16:11
@dvdmn exporting a database's scripts is *completely* different from backup. I realize some products don't offer backup/restore functionality or worse, muddle the two concepts. This often happens due to historical reasons: lacking backup in the past, some products called exporting/scripting by that name. Now that they *do*, they have to keep solving term conflicts, by talking about physical and logical backups, then get mixed up when they talk about scripting — Panagiotis Kanavos, Apr 15 '16 at 07:33
I agree, but it does not change the fact that sql server backup system is not perfect or even not good under some cases. I would expect something better considering the licensing fee. PS: Little bit irrelevant but also the connection manager sucks :) — dvdmn, Apr 15 '16 at 15:00
Cynical me says that it's a ploy by Microsoft to slowly force people to upgrade their MSSQL. It seems entirely arbitrary that you can't restore an earlier version into a later version; they're both on v10, so can't be radically different. — Steve Smith, Mar 12 '19 at 10:31

score 143 · Answer 1 · answered Jun 02 '15 at 10:31

143

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.

answered Jun 02 '15 at 10:31

veljasije

6,722
12
48
79

10

This is an answer that deserves more attention. It actually does Just Work for most cases. If you have SQL users on there that are orphaned, though, the Export operations will fail. You can always remove/add those bits manually to otherwise transcend SQL versions. – Doctor Blue Jun 02 '15 at 12:35
5

I just tried this, to create a DB created on MSSQLEX2014 (V12) on my MSSQLEX2012 (V11). I just get a "Microsoft.Data.Tools.Schema.Sql.Sql120DatabaseSchemaProvider is not valid" error with no way to change it. – Craig Jul 06 '15 at 10:45
Didn't work for me attempting to export a database with 2012 compatibility from SQL2014 to SQL2012. – Paul Taylor Feb 05 '16 at 12:21
5

Importing from SQL Server 2014 to 2012 using a .bakpac DOES work, but it requires the correct version of SSMS. For example, I use SSMS 2016 CTP3 and it worked flawlessly. It did NOT work for me using SSMS 2012. I have not tested SSMS 2014. – Greg Gum Apr 12 '16 at 18:01
3

Anyone knows if one can **script** this procedure in T-SQL? UPDATE (answering myself after a bit of googling): you can automate this via `sqlpackage.exe` command line. Google for more. – Alex from Jitbit Jul 03 '17 at 12:14
2

This is the only solution that worked for me for downgrading quite large/complicated SQL2017 to SQL2016. Performance also very good. – Keith Blows Feb 27 '19 at 13:29
I get a failure because of views in my database that reference tables in an archive database. The error is external references not supported. Any ideas how to fix this? – CB_Ron Jul 22 '19 at 23:50
This method does not work when downgrading from 2016 to 2014 because of T-SQL directives not compatible with 2014 version. – sparrow Sep 24 '20 at 12:06
This method saved by sanity. Absolutely way better than Export Data functionality by MILES. – Luke Oct 22 '22 at 03:28

score 81 · Accepted Answer · answered May 31 '11 at 05:36

81

No, is not possible to downgrade a database. 10.50.1600 is the SQL Server 2008 R2 version. There is absolutely no way you can restore or attach this database to the SQL Server 2008 instance you are trying to restore on (10.00.1600 is SQL Server 2008). Your only options are:

upgrade this instance to SQL Server 2008 R2 or
restore the backup you have on a SQL Server 2008 R2 instance, export all the data and import it on a SQL Server 2008 database.

answered May 31 '11 at 05:36

Remus Rusanu

288,378
40
442
569

i can't upgrade clients live server because of permission restrictions. and my database size is over 2.5GB so i couldn't create data script because of memory exception error. – May 31 '11 at 07:05
11

@Gaurav Don't script out the data - instead, script out the entire database (tables, procs, triggers, constraints, etc) without data, create the new database, then right click the source database, go to "Tools", then "Export Data" to transfer the contents of the tables to your database. – Jim McLeod May 31 '11 at 07:50
30

I am very suspicious of a pat answer "there is no possible way" - that just means you haven't yet found the way. There may be many reasons why you want to do this, so this is not a helpful answer. – Jay Imerman Aug 26 '14 at 19:27
6

I disagree, @Jay, sometimes "you can't" is the perfect answer, though it's more useful to offer workarounds where available. – Russell Fox May 02 '17 at 20:31
How do I upgrade the sql server instance. I installed the newer version of sql server. But the instance is still on old one. – Zhang Dec 23 '19 at 09:25
1

@Zhang if you have questions, ask them as questions, not as comment. – Remus Rusanu Dec 23 '19 at 19:50
2

the veljasije's answer has a simple workaround. Don't understand why a "no is not possible" is marked as answer, when there is a simple solution to achieve it without any upgrade. – serge Apr 06 '21 at 10:00

score 42 · Answer 3 · answered May 02 '16 at 11:06

42

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.

answered May 02 '16 at 11:06

Smit Patel

2,992
1
26
44

1

It's 2020 and that's the best answer now! – d.popov Aug 04 '20 at 09:01
3

@d.popov only if the database is a couple of gigabytes, otherwise the file will be too large to be read by SSMS. – Josh J Dec 01 '20 at 14:58

score 38 · Answer 4 · edited Feb 12 '18 at 02:31

38

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

edited Feb 12 '18 at 02:31

theMayer

15,456
7
58
90

answered May 28 '13 at 19:43

Ohad Schneider

36,600
15
168
198

8

Script generation is unreliable for substantial databases, avoid if at all possible. – Chris May 29 '15 at 19:50
Both links at the bottom are 404. – Sнаđошƒаӽ Jun 03 '20 at 09:31
1

According to https://learn.microsoft.com/en-us/sql/relational-databases/databases/use-the-copy-database-wizard?view=sql-server-ver15 the Copy Database option will not work because "A database cannot be moved or copied to an earlier version of SQL Server" – sparrow Sep 24 '20 at 11:14

score 31 · Answer 5 · answered Jun 18 '13 at 13:09

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.

Red Gate Sql Clone does not support cloning between different version of SQL server, reporting exactly the same error which I get when trying to do this manually. — DarkDeny, Dec 14 '17 at 11:00

score 19 · Answer 6 · edited Sep 21 '12 at 19:26

19

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

edited Sep 21 '12 at 19:26

Taryn

242,637
56
362
405

answered Sep 11 '12 at 14:23

Joy Walker

532
5
13

4

Note that this will not work if the target is SQL server express "The destination server cannot be a SQL Server 2005 or later Express instance." http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/a40a5867-a46a-478f-bf0d-52cc423b712b/ – Ohad Schneider May 28 '13 at 19:17
3

It also is not possible to copy a database from SQL Server 2012 to SQL Server 2008 this way. – Twinkles Oct 24 '13 at 12:55
1

Copy database *is* a Detach/Attach operation – Panagiotis Kanavos Apr 13 '16 at 14:04
1

Thanks for this hint! That helped me very much. I copied the data from SQL 2008 R2 to SQL 2008. – dns_nx May 10 '16 at 08:10
1

I don't see this option in SSMS 18.4. YMMV. – Ben Nov 16 '20 at 22:54

score 8 · Answer 7 · edited Mar 01 '20 at 07:15

8

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.

edited Mar 01 '20 at 07:15

Kate Orlova

3,225
5
11
35

answered Feb 10 '15 at 00:14

mathewsun

373
6
14

6

This is very good solution but works very poor with larger databases. – veljasije Apr 12 '16 at 19:41

score 8 · Answer 8 · answered Sep 16 '11 at 06:11

8

You can try this.

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

answered Sep 16 '11 at 06:11

Ishtiyaq Khan

81
1
1

score 5 · Answer 9 · answered May 15 '12 at 20:55

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.

hi @motogeek, i have the same issue, but i have the R2 on production server and 2008 on local, in this case i cannot use the import and export data. what would be the ideal solution for this? — Abbas, Jul 22 '13 at 18:33

score 2 · Answer 10 · edited Nov 18 '16 at 06:22

2

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

edited Nov 18 '16 at 06:22

abpatil

916
16
20

answered Nov 18 '16 at 05:27

Vishe

3,383
1
24
23

score 0 · Answer 11 · answered Jun 22 '16 at 13:30

0

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.

answered Jun 22 '16 at 13:30

tony.wiredin

617
8
21

That tool has been replaced with the SQL Server Data Migration Assistant. See: https://stackoverflow.com/questions/46517060/what-happened-to-the-sql-server-database-migration-wizard – ahwm May 01 '18 at 16:51

tonderaimuchada · Answer 12 · 2020-01-31T15:41:05.120

0

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:

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

edited Jan 31 '20 at 15:41

answered Jan 31 '20 at 15:34

tonderaimuchada

215
2
6

score 0 · Answer 13 · answered Mar 19 '20 at 20:29

0

You can generate script from Task menu

For detailed reference

How to migrate a SQL Server database to a lower version

answered Mar 19 '20 at 20:29

Sajeeb Chandan Saha

705
10
12

SQL Server database backup restore on lower version

13 Answers13

Linked

Related