Database was backed up on a server running version 13

Question

Database was backed up on a server running version 13

asked Oct 7, 2020 in SQL Server by Imran Ahmed
retagged Oct 8, 2020 by Mohamed El-Qassas

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

answered Oct 8, 2020 by Mohamed El-Qassas
selected Oct 11, 2020 by Imran Ahmed

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:

Using a third-party tool.
Upgrading the current SQL Server instance to a newer version (the same SQL version that you have backed up your database or higher).
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.
Also, the Export Data option will not copy the schema, it will only move the data.
The Generate Scripts option will generate scripts for schema and data but it's not suitable for large databases. instead, you should use SSIS.
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.