I'm getting an error when trying to restore a SQL Server 2016 backup on SQL Server 2014 with compatibility mode 110
当我尝试在SQL Server 2014上恢复SQL Server 2016备份时，我遇到了一个错误，它的兼容性模式为110。
Msg 3169, Level 16, State 1, Line 38
The database was backed up on a server running version 13.00.4001. That version is incompatible with this server, which is running version 12.00.5532. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.Msg 3169，级别16，状态1，第38行数据库备份在一个运行版本13.00.4001的服务器上。该版本与正在运行版本12.00.5532的服务器不兼容。要么在支持备份的服务器上恢复数据库，要么使用与此服务器兼容的备份。
Msg 3013, Level 16, State 1, Line 38
RESTORE DATABASE is terminating abnormally.
An error was encountered during execution of batch. Exiting.Msg 3013, 16级，状态1，第38行恢复数据库异常终止。在执行批处理时遇到错误。退出。
Upgrade to SQL Server 2016 is not a option for this solution.
升级到SQL Server 2016不是这个解决方案的选项。
3 个解决方案
#1
3
You CANNOT ever restore a backup from a newer version of SQL Server (e.g. 2016) onto a server with an older version (e.g. SQL Server 2014) - there's no documented (or undocumented) way, no method, no workaround, no hack to achieve this - it's just not possible.
您永远无法将更新版本的SQL Server(例如2016)的备份恢复到具有旧版本的服务器上(例如SQL Server 2014)——没有文档化(或无文档化)的方法、方法、变通方法、黑客来实现这一点——这是不可能的。
The internal database structures are just too different to provide a "backwards" compatibility path. And fiddling with the compatibility level also doesn't help, since this doesn't really change the internals of the database file - it just enables/disables certain T-SQL features.
内部数据库结构差异太大，无法提供“向后”兼容路径。修改兼容性级别也没有帮助，因为这并没有真正改变数据库文件的内部结构——它只是启用/禁用某些T-SQL特性。
The simplest solution is to just use the same version of SQL Server on all your systems. That way, you can easily back up from one machine, and restore that database onto another one.
最简单的解决方案是在所有系统上使用相同版本的SQL Server。这样，您可以很容易地从一台机器上备份，并将该数据库恢复到另一台机器上。
If that isn't possible, then you must use T-SQL scripts to transport changes in structure and/or contents (data) from the newer version to the older. You can use tools like Redgate SQL Compare and SQL Data Compare to help you with generating those T-SQL scripts, or you can author those scripts yourself. Or you build some other means of synchronizing the two databases with each other.
如果不可能，那么必须使用T-SQL脚本将结构和/或内容(数据)的更改从新版本传输到旧版本。您可以使用Redgate SQL Compare和SQL Data Compare等工具来帮助您生成这些T-SQL脚本，或者您可以自己编写这些脚本。或者构建一些其他方法来同步两个数据库。
Or if you need to move "only" the data, you could check into the Import/Export wizard, and dump your data from SQL Server 2016 into an intermediate format (Access, Excel) and re-import it on the older SQL Server from there.
或者，如果您需要“仅”移动数据，您可以检查导入/导出向导，并将您的数据从SQL Server 2016转储到中间格式(Access, Excel)中，然后在旧的SQL服务器上重新导入数据。
#2
0
This is not possible.
这是不可能的。
You require an instance of SQL Server 2016 (or later) to restore a backup made with SQL Server 2016.
您需要一个SQL Server 2016(或更高版本)的实例来恢复由SQL Server 2016所做的备份。
The compatibility version of a database is not relevant to the version of SQL Server required to restore a backup of the database. SQL Server supports backward compatibility, so you can restore a backup made from SQL Server 2014 on SQL Server 2016, but the opposite is not and never has been supported. No option or configuration will allow you to circumvent this limitation.
数据库的兼容性版本与恢复数据库备份所需的SQL Server版本不相关。SQL Server支持向后兼容，所以您可以在SQL Server 2016上恢复SQL Server 2014的备份，但相反的情况是，从来都不支持。任何选项或配置都不允许您绕过这个限制。
#3
0
How about setting up a linked server or some sort of export/import as a workaround.
如何设置一个链接服务器或某种导出/导入作为解决方案。
#1
3
You CANNOT ever restore a backup from a newer version of SQL Server (e.g. 2016) onto a server with an older version (e.g. SQL Server 2014) - there's no documented (or undocumented) way, no method, no workaround, no hack to achieve this - it's just not possible.
您永远无法将更新版本的SQL Server(例如2016)的备份恢复到具有旧版本的服务器上(例如SQL Server 2014)——没有文档化(或无文档化)的方法、方法、变通方法、黑客来实现这一点——这是不可能的。
The internal database structures are just too different to provide a "backwards" compatibility path. And fiddling with the compatibility level also doesn't help, since this doesn't really change the internals of the database file - it just enables/disables certain T-SQL features.
内部数据库结构差异太大，无法提供“向后”兼容路径。修改兼容性级别也没有帮助，因为这并没有真正改变数据库文件的内部结构——它只是启用/禁用某些T-SQL特性。
The simplest solution is to just use the same version of SQL Server on all your systems. That way, you can easily back up from one machine, and restore that database onto another one.
最简单的解决方案是在所有系统上使用相同版本的SQL Server。这样，您可以很容易地从一台机器上备份，并将该数据库恢复到另一台机器上。
If that isn't possible, then you must use T-SQL scripts to transport changes in structure and/or contents (data) from the newer version to the older. You can use tools like Redgate SQL Compare and SQL Data Compare to help you with generating those T-SQL scripts, or you can author those scripts yourself. Or you build some other means of synchronizing the two databases with each other.
如果不可能，那么必须使用T-SQL脚本将结构和/或内容(数据)的更改从新版本传输到旧版本。您可以使用Redgate SQL Compare和SQL Data Compare等工具来帮助您生成这些T-SQL脚本，或者您可以自己编写这些脚本。或者构建一些其他方法来同步两个数据库。
Or if you need to move "only" the data, you could check into the Import/Export wizard, and dump your data from SQL Server 2016 into an intermediate format (Access, Excel) and re-import it on the older SQL Server from there.
或者，如果您需要“仅”移动数据，您可以检查导入/导出向导，并将您的数据从SQL Server 2016转储到中间格式(Access, Excel)中，然后在旧的SQL服务器上重新导入数据。
#2
0
This is not possible.
这是不可能的。
You require an instance of SQL Server 2016 (or later) to restore a backup made with SQL Server 2016.
您需要一个SQL Server 2016(或更高版本)的实例来恢复由SQL Server 2016所做的备份。
The compatibility version of a database is not relevant to the version of SQL Server required to restore a backup of the database. SQL Server supports backward compatibility, so you can restore a backup made from SQL Server 2014 on SQL Server 2016, but the opposite is not and never has been supported. No option or configuration will allow you to circumvent this limitation.
数据库的兼容性版本与恢复数据库备份所需的SQL Server版本不相关。SQL Server支持向后兼容，所以您可以在SQL Server 2016上恢复SQL Server 2014的备份，但相反的情况是，从来都不支持。任何选项或配置都不允许您绕过这个限制。
#3
0
How about setting up a linked server or some sort of export/import as a workaround.
如何设置一个链接服务器或某种导出/导入作为解决方案。