将数据库迁移到旧版本的MS SQL Server

您有一个MS SQL Server数据库，需要将其转移到另一台物理计算机。 您已经进行了备份，并乐于进行恢复。 但是事实证明，在要传输数据库的计算机上，安装了旧版本的MS SQL Server。 堆栈溢出可确保您一切都不好。 但是真的是这样吗？


当然，将数据库从新版本转移到旧版本不是经典的，也不是最正确的方案。 但是，通常以这样的方式创建数据库，即它们支持越来越多的SQL新版本（例如从2008 R2开始），因为 与MS SQL的直接兼容性非常出色。 并且，例如，您的客户端本身已经安装了MS SQL 2016，并且您在测试服务器上安装了MS SQL 2014进行开发，并且您想扩展客户端库以查找数据混乱的地方。

微软否认了这个问题-他们说他们没有向后兼容性，也没有破产。 在新服务器中创建的备份无法还原到旧服务器。 是的，他们拥有DTS，复制数据库，导出-导入等工具。 但是它们是如此不便和繁琐，以至于无法正常迁移包含许多表的大型数据库以使用它们。 无论如何，我个人都没有成功。

是的，您可以为整个数据库（包括数据）生成SQL脚本。 但是想像一下，您在数据库中有一堆具有大数据的Blob字段，并且通常整个数据库的大小为500+ GB。 想象一下这样一个脚本将花费多少，它将产生并执行多少时间。

因此，任务是从旧版本的MS SQL Server的新版本中准确地重新创建数据库（结构和数据）。 我想出了一个我想分享的相当简单的解决方案。 当然，此解决方案有很多局限性，但我仍然认为它比DTS和脚本更好。

第一个限制是您需要通过MS SQL Management Studio访问新旧服务器。 如果这不可能，那么在要从中传输数据库的计算机上应该是可行的，安装您需要在其中传输数据库的SQL版本，以便首先将数据库本地传输到此版本，然后将其拖动到备份中或直接通过* df拖动数据库文件（通过分离/附加）到新计算机（在这种情况下，SQL Server的版本已经匹配）。

另一个限制是您将需要一个没有数据的数据库模式脚本（所有对象，包括表，索引，常量，存储过程，触发器等），并且没有数据，并且创建外键约束的说明应转到此脚本。最后，与用于创建表本身的脚本分开。

我将简要描述数据传输算法本身。 所有操作均在与您要将数据库传输到的服务器连接的Management Studio会话中执行。

1）在新服务器上，创建一个空数据库，其文件和文件组与可移植数据库相同。

2）使用数据库模式脚本，我们创建所有数据库对象（表，索引，视图，触发器，存储过程和函数），但不创建外键约束。 您目前无法创建FK，因为 它们会干扰数据插入。

3）我们将连接数据库的数据库作为链接服务器连接到数据库，以便您可以在查询中使用旧数据库到新数据库。

EXEC sp_addlinkedserver @server=N'LinkedServerAlias', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'LinkedServerHost\LinkedServerName'; EXEC sp_addlinkedsrvlogin 'LinkedServerUser', 'false', null, 'RealUser', 'RealUserPassword'; 

4）因为 数据库结构相同，我们将使用内置的存储过程sp_msforeachtable，它使您可以对每个数据库表执行查询，以生成一个脚本，以通过查询以下形式将数据从旧数据库传输到新数据库

 INSERT INTO ? SELECT * FROM ? 

sp_msforeachtable代替每个表的名称，而不是一个问号，并多次执行查询（每个表一次）。

在这里，我遇到了数量最多的耙子。

a）第一个问题是对于具有IDENTITY字段的表，您必须调用：

 SET IDENTITY_INSERT ON; --INSERT INTO ... ( ); SET IDENTITY_INSERT OFF; 

b）第二个问题是您无法在没有IDENTITY字段的表上进行此调用，因此您需要动态确定表是否具有IDENITY列。

这可以通过以下查询完成：

 SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME='SomeTable') AND (COLUMNPROPERTY(object_id('dbo.SomeTable'), COLUMN_NAME, 'IsIdentity') = 1) 

c）问题三，事实证明，在IDENITY_INSERT ON模式下，您不能执行

 INSERT INTO ... SELECT * FROM ... 

，但您需要列出特定字段。

您可以使用以下查询在一行中列出表字段：

 SELECT SUBSTRING( (SELECT ', ' + QUOTENAME(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SomeTable' ORDER BY ORDINAL_POSITION FOR XML path('')), 3, 200000); 

4）我们为所有表生成一个插入脚本：


5）我们执行生成的数据传输脚本

6）我们执行一个脚本来创建所有外键约束（现在已经可以）。

7）完成！ 您将数据库从新的SQL Server转移到了旧的SQL Server，尽管这被认为是不可能的。 而且，传输仅比网络上的数据传输速率慢一倍半。 非常快。

8）我们自己清理（断开链接服务器的连接）：

 EXEC sp_droplinkedsrvlogin 'LinkedServerUser', null; sp_dropserver 'LinkedServerAlias'; 

方法的局限性。

1）使用类似的方法，将不可能传输其中存在XML类型的列的表。
当然，还有许多其他限制，例如 我以这种方式传输的数据库没有使用许多SQL Server功能。 您可以在评论中写有关限制的内容，我将在其中添加一篇文章。

感谢您的关注！ 希望它可以帮助某人。