Welcome to deBUG.to Community where you can ask questions and receive answers from Microsoft MVPs and other experts in our community.
1 like 0 dislike
12.9k views
in SQL Server by 5 7 17
retagged by

I have tried to restore a dabatabase backup that taken from SQL Server 2016 to SQL Server 2012 but I got this error

Microsoft SQL Server Management Studio

Restore of database failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The database was backed up on a server running version 13.00.1601. That version is incompatible with this server, which is running version 11.00.2218. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.SmoExtended)

the database was backed up on a server running version 13

I also have set the database compatability level to SQL SQL Server 2012 then take a new backup but still not working

database compatibility level

My question is How can I restore a database backup from a newer version to an older version in SQL Server?


1 Answer

2 like 0 dislike
by 56 122 250
selected by
 
Best answer

Restore database backup from newer version to older version

Unfortunately, the backup/restore or attach/detach process doesn't support backward compatibility in SQL Server, it only supports forward compatibility.

  • Backward compatibility means restore from a newer version to an older version.
  • Forward compatibility means restore from an older version to a newer version.

Therefore, if you have tried to perform a downgrade using attach/detach or backup/restore database, you will encounter this error.

The database was backed up on a server running version 13.00. 
That version is incompatible with this server, which is running version 11.00. 
Either restore the database on a server that supports the backup or use a backup that is compatible with this server.

How to solve "The database was backed up on a server running version 13.00"?

To overcome this issue, you can use one of the below-proposed workarounds:

  1. Using a third-party tool.
  2. Upgrading the current SQL Server instance to a newer version (the same SQL version that you have backed up your database or higher).
  3. Using "Generate Scripts" to generate data and schema (Only suitable for small databases).

Check the detail steps to generate scripts in SQL Server at Missing data in SQL Generate Scripts


Keep in Minds

  • A database cannot be moved or copied to an earlier version of SQL Server, so the Copy Database is not an option.

    Copy Database in SQL Server

  • Also, the Export Data option will not copy the schema, it will only move the data.

    Export data in SQL Server

  • The Generate Scripts option will generate scripts for schema and data but it's not suitable for large databases. instead, you should use SSIS.

    Generate Scripts in SQL server

  • The Export Data-Tier Application includes schema and data but this option will not work from SQL Server 2016 to 2012 because some of the T-SQL directives are not compatible with SQL Server 2012 and 2014 versions.

  • The Export Data-Tier Application maybe work from SQL Server 2017 to SQL Server 2016 or from SQL Server 2014 to SQL Server 2012 using SQL Server Management Studio 2016 or higher.

    export data-tier application In SQL server

by 5 7 17
0 0
Thanks for this information. I generated sql scripts but the data is missing?
by 56 122 250
0 0
you are welcome, make sure that you have set the type of data script to schema and data. It would be also great to add a new question to provide you a detailed answer.
by 5 7 17
0 0
Sure, I will
by 5 7 17
0 0
by 56 122 250
0 0
I have answered your new question :) Please don't forget to upvote and mark the answer as accepted in case it helped you ♥
by 5 7 17
0 0
Done! thanks for this info
If you don’t ask, the answer is always NO!
...