SQL Server跨服务器迁移5亿条记录表遇登录超时求高效方案

SQL Server本身没有提供单独备份/还原单张表的内置功能，bcp工具+BULK INSERT语句是官方原生支持的最接近单表备份还原的方案，也是5亿条级大表迁移的最优选择，无需依赖两服务器直接网络连通，也不会出现生成脚本导出的内存不足问题，同时可完全避免重复数据。

步骤1：源端导出表数据为原生格式文件 #

在源服务器所在的机器（或者能正常访问源服务器的机器）上用管理员权限打开命令提示符，执行bcp命令：

bcp [源库名].[架构名].[表名] out D:\导出路径\表名.dat -S [源服务器地址\实例名] -U [源用户名] -P [源密码] -n -b 100000

参数说明：

• -n 采用SQL Server原生二进制格式导出，速度最快、不会出现编码转换问题
• -b 100000 每10万条提交一次，避免内存占用过高，可根据服务器配置调整到20-50万
• 整个导出过程不会占用源服务器过多内存，5亿条记录根据磁盘IO速度一般几十分钟到几小时可完成

步骤2：转移导出文件到目标服务器 #

将导出的.dat文件拷贝到目标服务器本地磁盘，建议用物理介质或者高速内网传输，避免网络瓶颈影响效率。

步骤3：目标端批量导入数据 #

如果目标表已经建好和源端完全一致的结构，直接执行BULK INSERT命令导入即可，为了避免重复，导入前可以先清空目标表（确认不需要保留目标表原有数据再执行）：

-- 可选：清空目标表避免重复，TRUNCATE比DELETE执行速度快、不会产生大量日志
TRUNCATE TABLE [目标库名].[架构名].[表名]

-- 批量导入
BULK INSERT [目标库名].[架构名].[表名]
FROM 'D:\导入路径\表名.dat'
WITH (
    BATCHSIZE = 100000,
    DATAFILETYPE = 'native',
    TABLOCK
)

参数说明：

• TABLOCK 采用表级锁，导入速度可提升30%以上
• BATCHSIZE 每10万条提交一次，避免日志暴涨和内存不足
• 导入完成后可以执行SELECT COUNT(*) FROM 目标表和源端计数对比，确认数据一致性

如果仍然想使用导出数据向导，可以先排查以下几个点解决连接问题：

• 确认目标服务器的SQL Server浏览器服务已经启动
• 确认目标服务器防火墙开放了SQL Server的1433端口（如果是命名实例需要开放对应的动态端口）
• 确认目标服务器允许远程连接：SQL Server管理 studio -> 服务器属性 -> 连接 -> 勾选「允许远程连接到此服务器」
• 填写目标服务器地址时如果是命名实例需要填写「IP\实例名」，而不是只填IP

不管使用哪种迁移工具，都可以通过以下方式避免重复：

• 迁移前清空目标表，优先用TRUNCATE命令
• 如果不能清空目标表，可先把数据导入到一个临时中间表，再用MERGE语句合并到正式表，根据主键判断去重

内容的提问来源于stack exchange，提问作者DDLearn