MS SQL Server用sqlcmd执行*.sql脚本备份还原问题及提速咨询

• 首先*.bak是SQL Server原生的二进制页级备份文件，还原时直接把数据页、索引页写入数据库文件，不需要解析SQL语句、不需要逐行维护索引/触发器，事务日志开销极低，属于底层数据拷贝级操作，速度自然极快。
• 而*.sql是纯文本SQL脚本，执行时需要逐句解析、编译、执行，默认单语句独立提交事务，同时插入数据时要实时维护索引、触发器，还要记录完整事务日志，6GB的脚本通常包含千万级的单行插入语句，多重开销叠加自然会慢到数天。
• 你当前使用的sqlcmd默认参数没有做性能优化，也放大了执行开销。

1. 提前调整数据库配置 #

• 执行脚本前将目标数据库的恢复模式改为简单模式，执行完成后再改回原模式，可减少90%以上的事务日志写入开销。
• 给SQL Server服务账号授予「执行卷维护任务」系统权限，开启即时文件初始化，避免数据库文件扩容时的零格式化等待。
• 提前在目标库建好表结构，执行数据插入前先禁用所有非聚集索引、触发器，全部数据插入完成后再重建索引、启用触发器，避免插入过程中频繁维护索引的额外开销。

2. 脚本预处理 #

• 在SQL脚本开头添加以下配置，减少不必要的开销：

SET NOCOUNT ON; -- 关闭行影响输出，减少日志写入
SET XACT_ABORT ON; -- 出错直接终止批处理，避免无效执行
SET IMPLICIT_TRANSACTIONS OFF;

• 如果脚本中是逐行INSERT语句，批量合并为单条INSERT包含多行值的格式，或者使用bcp工具直接导出导入表数据，执行效率可提升数十倍。
• 去掉脚本中不必要的GO分隔符，减少批处理编译次数。

3. sqlcmd参数优化 #

将执行命令调整为以下配置：

sqlcmd -S .\SQLEXPRESS -U SA -d testdbb -i whole_DB_backup.sql -o result.log -a 32767 -b -t 0

参数说明：

• -a 32767：设置最大网络数据包大小，减少IO往返开销
• -b：执行出错时直接终止，避免浪费时间
• -t 0：关闭查询超时限制，避免长执行任务被中断

4. 额外注意事项 #

SQL Server Express版本本身存在资源限制：最大使用1GB内存、4核CPU、单库最大容量10GB，如果你导入后数据量超过10GB会触发限制报错，建议尽量优先使用*.bak文件还原，这是效率最高的方案。

内容的提问来源于stack exchange，提问作者MD. Mohiuddin Ahmed