可以在SQL Server 2012上还原SQL Server 2014的备份吗？ [英] Possible to restore a backup of SQL Server 2014 on SQL Server 2012?

问题描述

我知道您不能（至少不容易）在SQL Server 2008上还原SQL Server 2012备份。但是它如何将SQL Server 2014还原到SQL Server 2012？

I know that you can't (at least not easily) restore a SQL Server 2012 backup on SQL Server 2008. But how does it work for SQL Server 2014 to SQL Server 2012 ?

在数据库级别具有可将兼容性模式调整为任何其他SQL Server版本的属性。

On database level there is the property to adjust the compatibility mode to any other SQL Server version.

这如何起作用或起作用？只会禁止2014年的功能吗？

How does this helps or work ? Will it only disallow the features from 2014?

说实话，我已经尝试恢复备份，但是2012年无法识别数据文件，因此我无法单击确定按钮开始还原过程。

To be honest I already tried to restore a backup, but 2012 didn't recognize the datafile, so I couldn't click ok Button to start the restore procedure.

我错过了一些重要的选择吗？

Did I miss some important option ?

推荐答案

您不能执行此操作-您不能从SQL Server的较新版本附加到<分离>或备份/还原数据库，直到旧版本-内部文件结构差异太大，无法支持向后兼容。在SQL Server 2014中，这仍然是正确的-您无法在其他2014盒以外的任何东西（或其他更新的东西）上还原2014备份。

You CANNOT do this - you cannot attach/detach or backup/restore a database from a newer version of SQL Server down to an older version - the internal file structures are just too different to support backwards compatibility. This is still true in SQL Server 2014 - you cannot restore a 2014 backup on anything other than another 2014 box (or something newer).

您可以通过以下方式解决此问题：

You can either get around this problem by

• 在您的所有服务器上使用相同版本的SQL Server机器-然后您可以轻松地在实例之间备份/还原数据库

• using the same version of SQL Server on all your machines - then you can easily backup/restore databases between instances

否则，您可以为这两种结构（表，视图，存储过程等）创建数据库脚本。以及内容（表中包含的实际数据）在SQL Server Management Studio（任务>生成脚本）中或使用第三方工具

otherwise you can create the database scripts for both structure (tables, view, stored procedures etc.) and for contents (the actual data contained in the tables) either in SQL Server Management Studio (Tasks > Generate Scripts) or using a third-party tool

，或者您可以使用第三方工具，例如Red-Gate的 SQL比较和 SQL数据比较在您之间进行区分 r源和目标，根据这些差异生成更新脚本，然后在目标平台上执行这些脚本；

or you can use a third-party tool like Red-Gate's SQL Compare and SQL Data Compare to do "diffing" between your source and target, generate update scripts from those differences, and then execute those scripts on the target platform; this works across different SQL Server versions.

兼容模式设置只控制哪个T -SQL功能可供您使用-可以帮助防止意外使用其他服务器上不可用的新功能。但这确实不更改 .mdf 文件的内部文件格式-这是不针对该特定文件的解决方案问题-无法在较旧的实例上从新版本的SQL Server还原备份的解决方案。

The compatibility mode setting just controls what T-SQL features are available to you - which can help to prevent accidentally using new features not available in other servers. But it does NOT change the internal file format for the .mdf files - this is NOT a solution for that particular problem - there is no solution for restoring a backup from a newer version of SQL Server on an older instance.

这篇关于可以在SQL Server 2012上还原SQL Server 2014的备份吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！