出導(dǎo)入.zip：數(shù)據(jù)庫(kù)遷移的腳本化解決方案)
簡(jiǎn)介這是一份基于 C# 開(kāi)發(fā)的 SQL Server 數(shù)據(jù)庫(kù)腳本導(dǎo)出/導(dǎo)入工具功能與 SQL Server 2014 Management Studio 的“生成腳本”類(lèi)似適合需要在 .NET 環(huán)境下批量備份表結(jié)構(gòu)、數(shù)據(jù)或遷移數(shù)據(jù)庫(kù)的開(kāi)發(fā)者使用也可作為 C# WinForm 數(shù)據(jù)庫(kù)編程的入門(mén)實(shí)例。壓縮包共 66 個(gè)文件體積僅 2.16MB主要包含 10 個(gè) C# 源碼文件、8 個(gè) SQL 腳本、4 個(gè)可直接運(yùn)行的 EXE 及配套 DLL/PDB 調(diào)試文件另有解決方案、項(xiàng)目工程、界面資源配置和編譯中間文件。其中源碼覆蓋主程序入口、WinForm 界面和數(shù)據(jù)庫(kù)輔助操作類(lèi)SQL 腳本可作為導(dǎo)出結(jié)果的參考或?qū)肽０濉Ｒ延?1488 人學(xué)習(xí)/下載。借助該工具用戶(hù)可以像 SSMS 一樣選擇庫(kù)表并生成 CREATE/INSERT 腳本也可將腳本反向?qū)肽繕?biāo)數(shù)據(jù)庫(kù)實(shí)現(xiàn)不同服務(wù)器間的庫(kù)結(jié)構(gòu)遷移工程目錄按源碼、編譯輸出與資源分類(lèi)組織適合研究數(shù)據(jù)庫(kù)腳本生成原理或作為內(nèi)部數(shù)據(jù)遷移工具二次開(kāi)發(fā)的基礎(chǔ)。1. SQLSERVER腳本導(dǎo)出導(dǎo)入.zip數(shù)據(jù)搬遷問(wèn)題的最短路徑接手一個(gè)老庫(kù)到新服務(wù)器的數(shù)據(jù)遷移數(shù)據(jù)庫(kù)超過(guò) 1TBSSMS 的“生成腳本”功能在這個(gè)量級(jí)下基本是擺設(shè)導(dǎo)出的腳本動(dòng)輒幾個(gè) GB 文本執(zhí)行起來(lái)連日志文件都撐不住。這時(shí)候我把目光轉(zhuǎn)向腳本方案。SQLSERVER腳本導(dǎo)出導(dǎo)入.zip 就是這類(lèi)工具包的常見(jiàn)形態(tài)把一批 bcp 命令、T-SQL 存儲(chǔ)過(guò)程、批處理封裝在一起按“導(dǎo)出—傳輸—導(dǎo)入—校驗(yàn)”四步完成數(shù)據(jù)搬遷。它適合三類(lèi)人被大表遷移折磨的 DBA、需要在無(wú)人值守環(huán)境定時(shí)備份項(xiàng)目數(shù)據(jù)的運(yùn)維以及想擺脫圖形界面、把數(shù)據(jù)流轉(zhuǎn)做到可復(fù)現(xiàn)和可版本化的開(kāi)發(fā)者。這篇筆記不聊圖形界面只講腳本方案怎么落地。2. 腳本包背后的三條路徑bcp、BULK INSERT 與動(dòng)態(tài) INSERT 的選型邏輯腳本包的核心不是某一個(gè)命令而是三套互補(bǔ)的導(dǎo)出導(dǎo)入路徑。拿到任何腳本包先看它用的是哪條路徑再判斷適合什么數(shù)據(jù)量。2.1 bcp千萬(wàn)級(jí)大表導(dǎo)出的默認(rèn)答案bcp 是 SQL Server 自帶的命令行工具不依賴(lài) SSMS在 Windows 的 cmd、PowerShell 或者 Linux 的 mssql-tools 里都能調(diào)用。它的典型用法是bcp 查詢(xún)語(yǔ)句 queryout 數(shù)據(jù)文件 -S 服務(wù)器 -U 賬號(hào) -P 密碼導(dǎo)出的是二進(jìn)制或文本格式的平面文件。選擇 bcp 的第一個(gè)理由是性能。底層走 OLEDB 或 ODBC導(dǎo)出時(shí)是流式寫(xiě)文件不會(huì)把整張表拉進(jìn)內(nèi)存配合-b參數(shù)按批提交日志增長(zhǎng)可控。第二個(gè)理由是增量能力queryout 可以接任意 WHERE 子句把某個(gè)時(shí)間段、某種狀態(tài)的記錄先導(dǎo)出來(lái)腳本包里的“增量備份”功能基本都是靠這個(gè)實(shí)現(xiàn)的。bcp SELECT [ID], [Name], [Amount], [CreateTime] FROM [MyDB].[dbo].[Orders] WHERE [CreateTime] 2024-01-01 queryout D:\db_backup\orders_2024.dat -S 192.168.1.10,1433 -U backup_user -P YourPassword -c -t | -r \n -b 5000 -m 100 -e D:\db_backup\orders_2024.err這段命令做了幾件事-c表示用字符模式讀寫(xiě)-t |指定字段分隔符為豎線(xiàn)-r \n指定行分隔符為換行-b 5000表示每 5000 行提交一次-m 100允許最多 100 條錯(cuò)誤-e指定錯(cuò)誤日志文件。這里最容易忽略的是-m如果不寫(xiě)bcp 遇到第一條錯(cuò)誤就中斷如果寫(xiě)太大錯(cuò)誤記錄會(huì)淹沒(méi)在文件里腳本包一般建議設(shè) 50 到 100。2.2 BULK INSERT把數(shù)據(jù)文件快速裝進(jìn)目標(biāo)庫(kù)bcp 負(fù)責(zé)把數(shù)據(jù)導(dǎo)出去BULK INSERT 負(fù)責(zé)把文件裝回來(lái)這是腳本包里最常見(jiàn)的配對(duì)。BULK INSERT 是一條 T-SQL 語(yǔ)句可以直接在 SSMS 或腳本里執(zhí)行但它有一個(gè)硬約束文件路徑必須是 SQL Server 服務(wù)進(jìn)程能訪(fǎng)問(wèn)到的路徑不是你客戶(hù)端電腦的本地路徑。BULK INSERT [MyDB].[dbo].[Orders] FROM ND:\db_backup\orders_2024.dat WITH ( FIELDTERMINATOR |, ROWTERMINATOR \n, FIRSTROW 1, BATCHSIZE 5000, TABLOCK, CODEPAGE 65001 );FIELDTERMINATOR和ROWTERMINATOR必須和 bcp 導(dǎo)出的參數(shù)一致否則導(dǎo)入時(shí)列對(duì)不上。FIRSTROW 1表示從文件第一行開(kāi)始讀如果文件有標(biāo)題行要改成2。TABLOCK是性能關(guān)鍵它允許目標(biāo)表在導(dǎo)入期間使用最小日志記錄速度能提升一倍以上代價(jià)是導(dǎo)入期間這張表基本處于排他鎖狀態(tài)不適合在線(xiàn)業(yè)務(wù)。CODEPAGE 65001是把文件當(dāng)作 UTF-8 讀后面避坑章節(jié)會(huì)專(zhuān)門(mén)講編碼問(wèn)題。2.3 動(dòng)態(tài) INSERT小配置表的精細(xì)化處理bcp 和 BULK INSERT 適合大表但對(duì)于幾百行、幾千行的配置表、字典表用這兩個(gè)工具反而小題大做。腳本包里通常會(huì)另放一個(gè)生成 INSERT 語(yǔ)句的存儲(chǔ)過(guò)程把每行數(shù)據(jù)轉(zhuǎn)成可讀的文本 SQL。這樣做的價(jià)值在于可審查遷移前能直接看 SQL 內(nèi)容確認(rèn)數(shù)據(jù)沒(méi)被轉(zhuǎn)義、截?cái)?。DECLARE sql NVARCHAR(MAX) N; SELECT sql sql CASE WHEN sql N THEN N ELSE N UNION ALL SELECT END QUOTENAME(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME NConfigItem AND TABLE_SCHEMA Ndbo ORDER BY ORDINAL_POSITION; SET sql NSELECT * INTO #tmp FROM ( sql N) AS src;; PRINT sql;這段代碼演示的是動(dòng)態(tài)拼列名真正落地時(shí)還會(huì)把每一行用 FOR XML PATH 拼成INSERT INTO ... VALUES (...);。這里不想展開(kāi)太多因?yàn)閯?dòng)態(tài) INSERT 的坑非常明顯SQL 文本長(zhǎng)度有上限數(shù)據(jù)里帶單引號(hào)、換行符都會(huì)破壞語(yǔ)句結(jié)構(gòu)所以它只適合小表。腳本包里常見(jiàn)做法是把配置表單獨(dú)放進(jìn)一個(gè)sql\init_data.sql由維護(hù)者手工維護(hù)不參與大表流程。2.4 選型組合一次遷移任務(wù)里三種方案怎么配合一套成熟的腳本包不會(huì)只用一種方案。我一般會(huì)這樣分?jǐn)?shù)據(jù)量在百萬(wàn)級(jí)以下的維度表用動(dòng)態(tài) INSERT 生成可讀腳本千萬(wàn)級(jí)以上的事實(shí)表、流水表用 bcp 導(dǎo)出加 BULK INSERT 導(dǎo)入特別大的表超過(guò) 200GB則拆分成按月、按天導(dǎo)出多個(gè)文件再并行導(dǎo)。三者配合還有一個(gè)好處動(dòng)態(tài) INSERT 出錯(cuò)時(shí)可以人工看 SQLbcp 出錯(cuò)時(shí)可以看錯(cuò)誤文件排查路徑清晰。方案適合規(guī)模優(yōu)點(diǎn)缺點(diǎn)bcp queryout百萬(wàn)級(jí)以上流式導(dǎo)出、支持 WHERE 增量、不占內(nèi)存需要命令行環(huán)境、參數(shù)多BULK INSERT百萬(wàn)級(jí)以上T-SQL 內(nèi)執(zhí)行、可配批大小和事務(wù)文件必須放在服務(wù)端可訪(fǎng)問(wèn)路徑動(dòng)態(tài) INSERT萬(wàn)級(jí)以下可讀可審查、適合配置表SQL 長(zhǎng)度有限、大表性能差3. 從解壓到跑通一套可復(fù)現(xiàn)的導(dǎo)出導(dǎo)入腳本包這一章把完整流程走一遍。拿到 SQLSERVER腳本導(dǎo)出導(dǎo)入.zip 后按目錄結(jié)構(gòu)拆開(kāi)改配置跑導(dǎo)出再跑導(dǎo)入最后校驗(yàn)。3.1 目錄結(jié)構(gòu)與統(tǒng)一配置先定規(guī)矩再動(dòng)手腳本包解壓后通常長(zhǎng)這樣這也是我組織腳本的習(xí)慣。數(shù)據(jù)文件、日志、SQL 腳本、批處理各歸各的目錄避免一個(gè)文件夾里堆滿(mǎn)幾百個(gè)文件。SQLSERVER腳本導(dǎo)出導(dǎo)入/ export_all.bat import_all.bat config.ini check_count.sql sql/ import_tables.sql truncate_tables.sql rebuild_index.sql logs/ data/config.ini 是最先要改的東西。把服務(wù)器地址、數(shù)據(jù)庫(kù)名、賬號(hào)、密碼、目標(biāo)路徑、分隔符全部抽到配置里腳本本身不做任何硬編碼。[SERVER] SRC_SERVER192.168.1.10,1433 DST_SERVER192.168.1.20,1433 DB_NAMEMyDB [ACCOUNT] DB_USERbackup_user DB_PASSYourPassword [CONFIG] EXPORT_PATHD:\db_backup FIELD_SEPARATOR| ROW_SEPARATOR\n BATCH_SIZE5000注意密碼寫(xiě)在明文 ini 里在內(nèi)部工具包中沒(méi)問(wèn)題但如果這份腳本要交付給外部環(huán)境建議改成環(huán)境變量讀取。bat 文件里用%DB_USER%、%DB_PASS%引用配置值的方式不同腳本包各有差異但思路一致默認(rèn)值放進(jìn) ini命令行參數(shù)優(yōu)先級(jí)最高。3.2 批處理導(dǎo)出腳本循環(huán)調(diào)用 bcp 并收集錯(cuò)誤碼導(dǎo)出的批處理核心是一個(gè) for 循環(huán)遍歷表清單逐表調(diào)用 bcp。表清單可以是一個(gè)文本文件也可以直接寫(xiě)在數(shù)組變量里。echo off setlocal enabledelayedexpansion set EXPORT_PATHD:\db_backup set SERVER192.168.1.10,1433 set USERbackup_user set PASSYourPassword set DBMyDB if not exist %EXPORT_PATH% mkdir %EXPORT_PATH% for %%T in (Orders, Customers, Products, ConfigItem) do ( echo [%date% %time%] exporting %%T ... bcp SELECT * FROM [%DB%].[dbo].[%%T] queryout %EXPORT_PATH%\%%T.dat -S %SERVER% -U %USER% -P %PASS% -c -t | -r \n -b 5000 -m 100 -e %EXPORT_PATH%\%%T.err if !errorlevel! neq 0 ( echo [%date% %time%] ERROR - %%T export failed with code !errorlevel! %EXPORT_PATH%\export.log ) else ( echo [%date% %time%] OK - %%T %EXPORT_PATH%\export.log ) ) endlocal這段腳本有幾個(gè)關(guān)鍵點(diǎn)。setlocal enabledelayedexpansion必須打開(kāi)因?yàn)閑rrorlevel在循環(huán)里要用!errorlevel!而不是%errorlevel%讀取否則每次取到的都是循環(huán)開(kāi)始前的舊值。if not exist負(fù)責(zé)建目錄忘了建目錄會(huì)導(dǎo)致 bcp 報(bào)“無(wú)法打開(kāi)文件”。每個(gè)表錯(cuò)誤時(shí)只記錄日志而不中斷整個(gè)循環(huán)這樣一張表失敗不會(huì)拖垮整批任務(wù)。3.3 配合導(dǎo)入建表、清表、BULK INSERT 的順序不能亂導(dǎo)入前先確認(rèn)目標(biāo)庫(kù)表結(jié)構(gòu)已經(jīng)存在。腳本包通常會(huì)在導(dǎo)出階段順手生成一份建表腳本或者用 bcp format 命令生成格式文件作為建表參考。導(dǎo)入的順序是先 TRUNCATE 舊數(shù)據(jù)再執(zhí)行 BULK INSERT最后重建索引。echo off setlocal set SERVER192.168.1.20,1433 set USERbackup_user set PASSYourPassword set DBMyDB set DATA_PATHD:\db_backup sqlcmd -S %SERVER% -U %USER% -P %PASS% -d %DB% -i sql\truncate_tables.sql if errorlevel 1 goto :end sqlcmd -S %SERVER% -U %USER% -P %PASS% -d %DB% -i sql\import_tables.sql if errorlevel 1 goto :end sqlcmd -S %SERVER% -U %USER% -P %PASS% -d %DB% -i sql\rebuild_index.sql if errorlevel 1 goto :end echo [%date% %time%] import success goto :end :end endlocaltruncate_tables.sql里寫(xiě)的是對(duì)目標(biāo)表執(zhí)行TRUNCATE TABLE目的是讓導(dǎo)入可重復(fù)執(zhí)行。這里有一個(gè)順序問(wèn)題必須先清表再導(dǎo)入否則 BULK INSERT 會(huì)往已有數(shù)據(jù)后面追加第二次運(yùn)行時(shí)數(shù)據(jù)翻倍。import_tables.sql的內(nèi)容就是上一章展示的 BULK INSERT 語(yǔ)句每張表一段建議把TABLOCK打開(kāi)等全部導(dǎo)入完成后再重建索引比導(dǎo)入前保留索引快很多。3.4 校驗(yàn)環(huán)節(jié)行數(shù)、聚合值與樣本比對(duì)數(shù)據(jù)導(dǎo)完不等于遷完必須驗(yàn)證。最基礎(chǔ)的是行數(shù)比對(duì)源庫(kù)和目標(biāo)庫(kù)各跑一次 COUNT_BIG人工或腳本比對(duì)結(jié)果。更穩(wěn)的校驗(yàn)是同時(shí)比對(duì)主鍵的聚合校驗(yàn)和與行數(shù)這樣能發(fā)現(xiàn)同構(gòu)但數(shù)據(jù)錯(cuò)位的極端情況。-- 源庫(kù)執(zhí)行 SELECT COUNT_BIG(1) AS RowCnt, SUM(CAST(CHECKSUM([ID], [Name], [Amount], [CreateTime]) AS BIGINT)) AS AggKey FROM [MyDB].[dbo].[Orders]; -- 目標(biāo)庫(kù)執(zhí)行結(jié)果應(yīng)與源庫(kù)完全一致 SELECT COUNT_BIG(1) AS RowCnt, SUM(CAST(CHECKSUM([ID], [Name], [Amount], [CreateTime]) AS BIGINT)) AS AggKey FROM [MyDB].[dbo].[Orders];CHECKSUM聚合校驗(yàn)的核心是把每一行變成一個(gè)整數(shù)再對(duì)全表求和。只要有一行數(shù)據(jù)的某個(gè)字段不一致總和幾乎必然變化。要注意CHECKSUM的結(jié)果范圍是 int 有符號(hào)整數(shù)直接求和可能溢出所以要包一層CAST(... AS BIGINT)。如果兩張表的這兩條結(jié)果完全一致基本可以斷定數(shù)據(jù)遷移成功。4. 三個(gè)必調(diào)參數(shù)組規(guī)模、編碼、權(quán)限腳本能不能跑通一半看參數(shù)調(diào)得對(duì)不對(duì)。這一章講腳本包里最值得花時(shí)間的三個(gè)參數(shù)組。4.1 批大小與錯(cuò)誤上限把大事務(wù)拆小把失敗控制在文件里bcp 的-b和 BULK INSERT 的BATCHSIZE是同一個(gè)概念每 N 行提交一個(gè)事務(wù)。批越小單次事務(wù)占用的日志越少失敗后回滾的成本越低批越大導(dǎo)入吞吐越高但一旦中間失敗回滾范圍也越大。我的實(shí)踐經(jīng)驗(yàn)是1GB 以下的表用 1000 到 500010GB 以上的表用 10000 到 20000再往上收益就不明顯了。-m錯(cuò)誤上限要跟批大小聯(lián)動(dòng)-m 100配合-b 5000意味著最多只能容忍 100 行壞數(shù)據(jù)超過(guò)立刻終止防止錯(cuò)誤記錄把文件撐爆。如果你導(dǎo)出的數(shù)據(jù)來(lái)自業(yè)務(wù)庫(kù)建議把-m設(shè)為 0也就是一個(gè)錯(cuò)誤都不允許因?yàn)闃I(yè)務(wù)庫(kù)的數(shù)據(jù)理論上不該有壞行出現(xiàn)任何一條都說(shuō)明上游有問(wèn)題。e錯(cuò)誤文件會(huì)記錄行號(hào)、列號(hào)和錯(cuò)誤原因格式如下這是排查時(shí)最直接的入口。第 100 行列 3無(wú)法將值 abc-123 轉(zhuǎn)換為 bigint 第 105 行列 7字符串或二進(jìn)制數(shù)據(jù)將被截?cái)?.2 字符模式、Unicode 與格式文件亂碼和錯(cuò)列的根源bcp 的-c是字符模式數(shù)據(jù)按數(shù)據(jù)庫(kù)排序規(guī)則轉(zhuǎn)成字符串輸出-w是 Unicode 模式輸出 UTF-16 文件-C 65001指定使用 UTF-8 代碼頁(yè)。三者選錯(cuò)輕則中文變問(wèn)號(hào)重則直接導(dǎo)入失敗。導(dǎo)出參數(shù)文件編碼適用場(chǎng)景-c隨系統(tǒng)代碼頁(yè)純 ASCII 或數(shù)字內(nèi)容-wUTF-16含 nvarchar / nchar 字段-c -C 65001UTF-8跨平臺(tái)交換、現(xiàn)代應(yīng)用如果表里含nvarchar字段我一般直接用-w不要貪圖-c的緊湊。字符模式下非 Unicode 類(lèi)型轉(zhuǎn)成 varchar中文內(nèi)容取決于目標(biāo)庫(kù)排序規(guī)則一旦目標(biāo)庫(kù)的代碼頁(yè)和源庫(kù)不一致必亂。-w雖然文件體積大一倍但能完全避開(kāi)編碼問(wèn)題。格式文件是另一個(gè)層面當(dāng)字段類(lèi)型需要精確指定、列順序需要調(diào)整時(shí)用bcp ... format nul -f schema.fmt -c先生成格式文件再做修改。4.3 權(quán)限模型與連接方式讓每個(gè)賬號(hào)只做一件事腳本包在正式環(huán)境跑權(quán)限不足是最常見(jiàn)的“第一夜翻車(chē)點(diǎn)”。bcp 導(dǎo)出需要源庫(kù)的 SELECT 權(quán)限BULK INSERT 需要目標(biāo)庫(kù)的 INSERT 權(quán)限同時(shí)要求服務(wù)器級(jí)別的ADMINISTER BULK OPERATIONS權(quán)限sqlcmd 執(zhí)行腳本需要對(duì)應(yīng)庫(kù)的 EXECUTE 權(quán)限。我建議拆分兩個(gè)賬號(hào)導(dǎo)出賬號(hào)只給db_datareader導(dǎo)入賬號(hào)單獨(dú)建只給目標(biāo)庫(kù)的db_datawriter加ADMINISTER BULK OPERATIONS。不要復(fù)用 sa 賬號(hào)否則腳本一旦出錯(cuò)影響面會(huì)擴(kuò)大到整個(gè)實(shí)例。連接方式上-S參數(shù)里顯式寫(xiě)端口號(hào)-U -P走 SQL Server 身份驗(yàn)證如果走 Windows 身份驗(yàn)證批處理里要用-E但任務(wù)計(jì)劃程序運(yùn)行時(shí)注意賬號(hào)上下文否則會(huì)報(bào)“用戶(hù) NT AUTHORITY\ANONYMOUS 登錄失敗”。5. 腳本導(dǎo)出導(dǎo)入避坑指南五個(gè)現(xiàn)場(chǎng)翻車(chē)記錄腳本方案最大的風(fēng)險(xiǎn)不在命令本身而在環(huán)境差異。這一章記錄五個(gè)我實(shí)際踩過(guò)的坑現(xiàn)象、原因、解決辦法按順序列出。5.1 文件找不到路徑職責(zé)與客戶(hù)端/服務(wù)端位置的混淆現(xiàn)象是 bcp 導(dǎo)出正常但 BULK INSERT 報(bào)“無(wú)法大容量加載文件 D:\db_backup\orders.dat 不存在”。原因在于路徑職責(zé)不同bcp 是客戶(hù)端工具讀取客戶(hù)端文件系統(tǒng)的路徑BULK INSERT 是服務(wù)端 T-SQL 語(yǔ)句讀取 SQL Server 服務(wù)進(jìn)程所在機(jī)器的路徑。如果你在本地執(zhí)行 BULK INSERT而 SQL Server 跑在另一臺(tái)服務(wù)器上D 盤(pán)路徑自然不存在。解決方法是把數(shù)據(jù)文件上傳到 SQL Server 所在機(jī)器或者用 UNC 共享路徑\\server\share\orders.dat并保證 SQL Server 服務(wù)賬號(hào)有共享目錄的讀取權(quán)限。反過(guò)來(lái)bcp in 模式則是從客戶(hù)端讀取文件推送給服務(wù)端邏輯正好相反。5.2 導(dǎo)入錯(cuò)列數(shù)據(jù)里的分隔符、引號(hào)與行終止符現(xiàn)象是導(dǎo)入后某些行多了一列或者某個(gè)字段變成 NULL。最常見(jiàn)的原因是數(shù)據(jù)內(nèi)容里本身包含豎線(xiàn)|導(dǎo)出的字段分隔符和內(nèi)容撞車(chē)。比如備注字段“說(shuō)明A|B|C”被拆成了三個(gè)字段后面的列全部錯(cuò)位。解決方法是換用數(shù)據(jù)中極少出現(xiàn)的控制字符比如\x01十六進(jìn)制 0x01。bcp 的-t |改成-t \x01BULK INSERT 的FIELDTERMINATOR 0x01?；蛘吒纱嘤酶袷轿募槊恳涣酗@式定義類(lèi)型和終止符格式文件可以完全避開(kāi)“讀出來(lái)再猜列”的問(wèn)題。5.3 中文變問(wèn)號(hào)代碼頁(yè)和排序規(guī)則的拉鋸戰(zhàn)現(xiàn)象是導(dǎo)出文件用記事本打開(kāi)正常導(dǎo)入后數(shù)據(jù)庫(kù)里的中文全部變成問(wèn)號(hào)。原因通常是字符模式-c下源庫(kù)的排序規(guī)則是 Chinese_PRC_CI_AS導(dǎo)出時(shí)按系統(tǒng) ANSI 代碼頁(yè)轉(zhuǎn)出目標(biāo)庫(kù)導(dǎo)入時(shí)又按自己的代碼頁(yè)解析中間至少做了一層有損轉(zhuǎn)換。解決方法是含中文或任何非 ASCII 字符的表導(dǎo)出時(shí)改用-wBULK INSERT 里對(duì)應(yīng)改成ROWTERMINATOR不變但不要寫(xiě)CODEPAGE。如果你堅(jiān)持用 UTF-8導(dǎo)出加-C 65001導(dǎo)入側(cè)寫(xiě)CODEPAGE 65001兩邊缺一不可。5.4 身份列沖突IDENTITY_INSERT 與 seed 重置現(xiàn)象是導(dǎo)入后目標(biāo)表的自增列值跟源表完全對(duì)不上或者導(dǎo)入時(shí)報(bào)“不能為表插入顯式值”。原因很直接源表有 IDENTITY 列bcp 導(dǎo)出時(shí)把自增值也導(dǎo)出來(lái)了但目標(biāo)表默認(rèn)不允許對(duì) IDENTITY 列做顯式插入。解決方法是導(dǎo)入前先SET IDENTITY_INSERT [表名] ON導(dǎo)入完成后執(zhí)行DBCC CHECKIDENT (表名, RESEED)重新校準(zhǔn)自增種子。腳本包的import_tables.sql里每張含 IDENTITY 列的表BULK INSERT 前后必須包這兩句否則二次導(dǎo)入時(shí)自增列會(huì)從錯(cuò)誤起點(diǎn)繼續(xù)跑。5.5 超時(shí)和中斷網(wǎng)絡(luò)包大小、防火墻與重跑策略現(xiàn)象是導(dǎo)出 1 小時(shí)左右任務(wù)卡住bcp 進(jìn)程還在但文件大小長(zhǎng)時(shí)間不變沒(méi)有任何報(bào)錯(cuò)。原因大多是網(wǎng)絡(luò)傳輸問(wèn)題或防火墻探測(cè)長(zhǎng)連接后靜默斷連。bcp 本身不提供精確的查詢(xún)超時(shí)參數(shù)但可以通過(guò)調(diào)節(jié)網(wǎng)絡(luò)包大小降低斷連概率同時(shí)讓腳本具備斷點(diǎn)重跑能力。bcp SELECT * FROM [MyDB].[dbo].[Orders] WHERE [CreateTime] 2024-01-01 queryout D:\db_backup\orders_2024.dat -S 192.168.1.10,1433 -U backup_user -P YourPassword -c -t | -r \n -b 5000 -a 4096-a 4096是網(wǎng)絡(luò)包大小默認(rèn)值偏保守調(diào)大后長(zhǎng)傳場(chǎng)景更穩(wěn)。同時(shí)給腳本加上“目標(biāo)文件已存在則跳過(guò)”或“按時(shí)間分片導(dǎo)出”的邏輯中斷后不用從頭跑。比如這個(gè)例子里的 WHERE 條件已經(jīng)按日期過(guò)濾重跑時(shí)把文件改成orders_2024_0901.dat只補(bǔ)跑缺失的區(qū)間即可。6. 把腳本包升級(jí)成無(wú)人值守的定時(shí)任務(wù)腳本跑通只是第一步真正讓這套方案有價(jià)值的是接進(jìn)任務(wù)計(jì)劃讓它每天安靜地完成導(dǎo)出導(dǎo)入。這里分享兩個(gè)我常用的技巧。6.1 日期變量與日志文件批處理里直接用%DATE%取日期很玄學(xué)不同系統(tǒng)區(qū)域設(shè)置會(huì)給出不同格式%DATE:~0,4%這種切割方式換個(gè)環(huán)境就翻車(chē)。我在生產(chǎn)環(huán)境的腳本包里統(tǒng)一改用 PowerShell 生成日期字符串再傳給批處理。$date Get-Date -Format yyyyMMdd $logDir D:\db_backup\logs if (-not (Test-Path $logDir)) { New-Item -ItemType Directory -Path $logDir | Out-Null } cmd /c export_all.bat | Out-File $logDir\export_$date.log -Encoding utf8 $exitCode $LASTEXITCODE exit $exitCode這段腳本把每天的導(dǎo)出日志按日期歸檔同時(shí)把批處理的退出碼原樣傳給任務(wù)計(jì)劃方便計(jì)劃程序把失敗任務(wù)標(biāo)注出來(lái)。日志文件只要保留最近 30 天的老文件用一個(gè)簡(jiǎn)單循環(huán)清理就行。6.2 任務(wù)計(jì)劃程序的調(diào)用與退出碼任務(wù)計(jì)劃程序里執(zhí)行 PowerShell 腳本時(shí)注意“起始于”目錄要設(shè)成腳本包所在目錄否則相對(duì)路徑全部失效。批處理里所有路徑都寫(xiě)成%~dp0開(kāi)頭是更穩(wěn)的做法這是指腳本自身所在目錄。set BASE_PATH%~dp0 set EXPORT_PATH%BASE_PATH%data set LOG_PATH%BASE_PATH%logs我吃過(guò)一次虧腳本在命令行手動(dòng)跑正常放到任務(wù)計(jì)劃里就報(bào)“系統(tǒng)找不到指定的路徑”后來(lái)發(fā)現(xiàn)是任務(wù)計(jì)劃的“起始于”留空了。從那以后我所有腳本包統(tǒng)一改成%~dp0拼絕對(duì)路徑再不依賴(lài)工作目錄。最后收個(gè)尾腳本導(dǎo)出導(dǎo)入這套玩法技術(shù)上并不復(fù)雜真正的價(jià)值在于把遷移過(guò)程變成可重復(fù)、可檢查、可自動(dòng)化的流水線(xiàn)。如果你最近也在為數(shù)據(jù)庫(kù)搬遷頭疼先拿一張小表跑通全流程再逐步放大希望幫到你。本文還有配套的精品資源點(diǎn)擊獲取 SQLSERVER腳本導(dǎo)出導(dǎo)入.zip：數(shù)據(jù)庫(kù)遷移的腳本化解決方案
簡(jiǎn)介這是一份基于 C# 開(kāi)發(fā)的 SQL Server 數(shù)據(jù)庫(kù)腳本導(dǎo)出/導(dǎo)入工具功能與 SQL Server 2014 Management Studio 的“生成腳本”類(lèi)似適合需要在 .NET 環(huán)境下批量備份表結(jié)構(gòu)、數(shù)據(jù)或遷移數(shù)據(jù)庫(kù)的開(kāi)發(fā)者使用也可作為 C# WinForm 數(shù)據(jù)庫(kù)編程的入門(mén)實(shí)例。壓縮包共 66 個(gè)文件體積僅 2.16MB主要包含 10 個(gè) C# 源碼文件、8 個(gè) SQL 腳本、4 個(gè)可直接運(yùn)行的 EXE 及配套 DLL/PDB 調(diào)試文件另有解決方案、項(xiàng)目工程、界面資源配置和編譯中間文件。其中源碼覆蓋主程序入口、WinForm 界面和數(shù)據(jù)庫(kù)輔助操作類(lèi)SQL 腳本可作為導(dǎo)出結(jié)果的參考或?qū)肽０濉Ｒ延?1488 人學(xué)習(xí)/下載。借助該工具用戶(hù)可以像 SSMS 一樣選擇庫(kù)表并生成 CREATE/INSERT 腳本也可將腳本反向?qū)肽繕?biāo)數(shù)據(jù)庫(kù)實(shí)現(xiàn)不同服務(wù)器間的庫(kù)結(jié)構(gòu)遷移工程目錄按源碼、編譯輸出與資源分類(lèi)組織適合研究數(shù)據(jù)庫(kù)腳本生成原理或作為內(nèi)部數(shù)據(jù)遷移工具二次開(kāi)發(fā)的基礎(chǔ)。1. SQLSERVER腳本導(dǎo)出導(dǎo)入.zip數(shù)據(jù)搬遷問(wèn)題的最短路徑接手一個(gè)老庫(kù)到新服務(wù)器的數(shù)據(jù)遷移數(shù)據(jù)庫(kù)超過(guò) 1TBSSMS 的“生成腳本”功能在這個(gè)量級(jí)下基本是擺設(shè)導(dǎo)出的腳本動(dòng)輒幾個(gè) GB 文本執(zhí)行起來(lái)連日志文件都撐不住。這時(shí)候我把目光轉(zhuǎn)向腳本方案。SQLSERVER腳本導(dǎo)出導(dǎo)入.zip 就是這類(lèi)工具包的常見(jiàn)形態(tài)把一批 bcp 命令、T-SQL 存儲(chǔ)過(guò)程、批處理封裝在一起按“導(dǎo)出—傳輸—導(dǎo)入—校驗(yàn)”四步完成數(shù)據(jù)搬遷。它適合三類(lèi)人被大表遷移折磨的 DBA、需要在無(wú)人值守環(huán)境定時(shí)備份項(xiàng)目數(shù)據(jù)的運(yùn)維以及想擺脫圖形界面、把數(shù)據(jù)流轉(zhuǎn)做到可復(fù)現(xiàn)和可版本化的開(kāi)發(fā)者。這篇筆記不聊圖形界面只講腳本方案怎么落地。2. 腳本包背后的三條路徑bcp、BULK INSERT 與動(dòng)態(tài) INSERT 的選型邏輯腳本包的核心不是某一個(gè)命令而是三套互補(bǔ)的導(dǎo)出導(dǎo)入路徑。拿到任何腳本包先看它用的是哪條路徑再判斷適合什么數(shù)據(jù)量。2.1 bcp千萬(wàn)級(jí)大表導(dǎo)出的默認(rèn)答案bcp 是 SQL Server 自帶的命令行工具不依賴(lài) SSMS在 Windows 的 cmd、PowerShell 或者 Linux 的 mssql-tools 里都能調(diào)用。它的典型用法是bcp 查詢(xún)語(yǔ)句 queryout 數(shù)據(jù)文件 -S 服務(wù)器 -U 賬號(hào) -P 密碼導(dǎo)出的是二進(jìn)制或文本格式的平面文件。選擇 bcp 的第一個(gè)理由是性能。底層走 OLEDB 或 ODBC導(dǎo)出時(shí)是流式寫(xiě)文件不會(huì)把整張表拉進(jìn)內(nèi)存配合-b參數(shù)按批提交日志增長(zhǎng)可控。第二個(gè)理由是增量能力queryout 可以接任意 WHERE 子句把某個(gè)時(shí)間段、某種狀態(tài)的記錄先導(dǎo)出來(lái)腳本包里的“增量備份”功能基本都是靠這個(gè)實(shí)現(xiàn)的。bcp SELECT [ID], [Name], [Amount], [CreateTime] FROM [MyDB].[dbo].[Orders] WHERE [CreateTime] 2024-01-01 queryout D:\db_backup\orders_2024.dat -S 192.168.1.10,1433 -U backup_user -P YourPassword -c -t | -r \n -b 5000 -m 100 -e D:\db_backup\orders_2024.err這段命令做了幾件事-c表示用字符模式讀寫(xiě)-t |指定字段分隔符為豎線(xiàn)-r \n指定行分隔符為換行-b 5000表示每 5000 行提交一次-m 100允許最多 100 條錯(cuò)誤-e指定錯(cuò)誤日志文件。這里最容易忽略的是-m如果不寫(xiě)bcp 遇到第一條錯(cuò)誤就中斷如果寫(xiě)太大錯(cuò)誤記錄會(huì)淹沒(méi)在文件里腳本包一般建議設(shè) 50 到 100。2.2 BULK INSERT把數(shù)據(jù)文件快速裝進(jìn)目標(biāo)庫(kù)bcp 負(fù)責(zé)把數(shù)據(jù)導(dǎo)出去BULK INSERT 負(fù)責(zé)把文件裝回來(lái)這是腳本包里最常見(jiàn)的配對(duì)。BULK INSERT 是一條 T-SQL 語(yǔ)句可以直接在 SSMS 或腳本里執(zhí)行但它有一個(gè)硬約束文件路徑必須是 SQL Server 服務(wù)進(jìn)程能訪(fǎng)問(wèn)到的路徑不是你客戶(hù)端電腦的本地路徑。BULK INSERT [MyDB].[dbo].[Orders] FROM ND:\db_backup\orders_2024.dat WITH ( FIELDTERMINATOR |, ROWTERMINATOR \n, FIRSTROW 1, BATCHSIZE 5000, TABLOCK, CODEPAGE 65001 );FIELDTERMINATOR和ROWTERMINATOR必須和 bcp 導(dǎo)出的參數(shù)一致否則導(dǎo)入時(shí)列對(duì)不上。FIRSTROW 1表示從文件第一行開(kāi)始讀如果文件有標(biāo)題行要改成2。TABLOCK是性能關(guān)鍵它允許目標(biāo)表在導(dǎo)入期間使用最小日志記錄速度能提升一倍以上代價(jià)是導(dǎo)入期間這張表基本處于排他鎖狀態(tài)不適合在線(xiàn)業(yè)務(wù)。CODEPAGE 65001是把文件當(dāng)作 UTF-8 讀后面避坑章節(jié)會(huì)專(zhuān)門(mén)講編碼問(wèn)題。2.3 動(dòng)態(tài) INSERT小配置表的精細(xì)化處理bcp 和 BULK INSERT 適合大表但對(duì)于幾百行、幾千行的配置表、字典表用這兩個(gè)工具反而小題大做。腳本包里通常會(huì)另放一個(gè)生成 INSERT 語(yǔ)句的存儲(chǔ)過(guò)程把每行數(shù)據(jù)轉(zhuǎn)成可讀的文本 SQL。這樣做的價(jià)值在于可審查遷移前能直接看 SQL 內(nèi)容確認(rèn)數(shù)據(jù)沒(méi)被轉(zhuǎn)義、截?cái)?。DECLARE sql NVARCHAR(MAX) N; SELECT sql sql CASE WHEN sql N THEN N ELSE N UNION ALL SELECT END QUOTENAME(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME NConfigItem AND TABLE_SCHEMA Ndbo ORDER BY ORDINAL_POSITION; SET sql NSELECT * INTO #tmp FROM ( sql N) AS src;; PRINT sql;這段代碼演示的是動(dòng)態(tài)拼列名真正落地時(shí)還會(huì)把每一行用 FOR XML PATH 拼成INSERT INTO ... VALUES (...);。這里不想展開(kāi)太多因?yàn)閯?dòng)態(tài) INSERT 的坑非常明顯SQL 文本長(zhǎng)度有上限數(shù)據(jù)里帶單引號(hào)、換行符都會(huì)破壞語(yǔ)句結(jié)構(gòu)所以它只適合小表。腳本包里常見(jiàn)做法是把配置表單獨(dú)放進(jìn)一個(gè)sql\init_data.sql由維護(hù)者手工維護(hù)不參與大表流程。2.4 選型組合一次遷移任務(wù)里三種方案怎么配合一套成熟的腳本包不會(huì)只用一種方案。我一般會(huì)這樣分?jǐn)?shù)據(jù)量在百萬(wàn)級(jí)以下的維度表用動(dòng)態(tài) INSERT 生成可讀腳本千萬(wàn)級(jí)以上的事實(shí)表、流水表用 bcp 導(dǎo)出加 BULK INSERT 導(dǎo)入特別大的表超過(guò) 200GB則拆分成按月、按天導(dǎo)出多個(gè)文件再并行導(dǎo)。三者配合還有一個(gè)好處動(dòng)態(tài) INSERT 出錯(cuò)時(shí)可以人工看 SQLbcp 出錯(cuò)時(shí)可以看錯(cuò)誤文件排查路徑清晰。方案適合規(guī)模優(yōu)點(diǎn)缺點(diǎn)bcp queryout百萬(wàn)級(jí)以上流式導(dǎo)出、支持 WHERE 增量、不占內(nèi)存需要命令行環(huán)境、參數(shù)多BULK INSERT百萬(wàn)級(jí)以上T-SQL 內(nèi)執(zhí)行、可配批大小和事務(wù)文件必須放在服務(wù)端可訪(fǎng)問(wèn)路徑動(dòng)態(tài) INSERT萬(wàn)級(jí)以下可讀可審查、適合配置表SQL 長(zhǎng)度有限、大表性能差3. 從解壓到跑通一套可復(fù)現(xiàn)的導(dǎo)出導(dǎo)入腳本包這一章把完整流程走一遍。拿到 SQLSERVER腳本導(dǎo)出導(dǎo)入.zip 后按目錄結(jié)構(gòu)拆開(kāi)改配置跑導(dǎo)出再跑導(dǎo)入最后校驗(yàn)。3.1 目錄結(jié)構(gòu)與統(tǒng)一配置先定規(guī)矩再動(dòng)手腳本包解壓后通常長(zhǎng)這樣這也是我組織腳本的習(xí)慣。數(shù)據(jù)文件、日志、SQL 腳本、批處理各歸各的目錄避免一個(gè)文件夾里堆滿(mǎn)幾百個(gè)文件。SQLSERVER腳本導(dǎo)出導(dǎo)入/ export_all.bat import_all.bat config.ini check_count.sql sql/ import_tables.sql truncate_tables.sql rebuild_index.sql logs/ data/config.ini 是最先要改的東西。把服務(wù)器地址、數(shù)據(jù)庫(kù)名、賬號(hào)、密碼、目標(biāo)路徑、分隔符全部抽到配置里腳本本身不做任何硬編碼。[SERVER] SRC_SERVER192.168.1.10,1433 DST_SERVER192.168.1.20,1433 DB_NAMEMyDB [ACCOUNT] DB_USERbackup_user DB_PASSYourPassword [CONFIG] EXPORT_PATHD:\db_backup FIELD_SEPARATOR| ROW_SEPARATOR\n BATCH_SIZE5000注意密碼寫(xiě)在明文 ini 里在內(nèi)部工具包中沒(méi)問(wèn)題但如果這份腳本要交付給外部環(huán)境建議改成環(huán)境變量讀取。bat 文件里用%DB_USER%、%DB_PASS%引用配置值的方式不同腳本包各有差異但思路一致默認(rèn)值放進(jìn) ini命令行參數(shù)優(yōu)先級(jí)最高。3.2 批處理導(dǎo)出腳本循環(huán)調(diào)用 bcp 并收集錯(cuò)誤碼導(dǎo)出的批處理核心是一個(gè) for 循環(huán)遍歷表清單逐表調(diào)用 bcp。表清單可以是一個(gè)文本文件也可以直接寫(xiě)在數(shù)組變量里。echo off setlocal enabledelayedexpansion set EXPORT_PATHD:\db_backup set SERVER192.168.1.10,1433 set USERbackup_user set PASSYourPassword set DBMyDB if not exist %EXPORT_PATH% mkdir %EXPORT_PATH% for %%T in (Orders, Customers, Products, ConfigItem) do ( echo [%date% %time%] exporting %%T ... bcp SELECT * FROM [%DB%].[dbo].[%%T] queryout %EXPORT_PATH%\%%T.dat -S %SERVER% -U %USER% -P %PASS% -c -t | -r \n -b 5000 -m 100 -e %EXPORT_PATH%\%%T.err if !errorlevel! neq 0 ( echo [%date% %time%] ERROR - %%T export failed with code !errorlevel! %EXPORT_PATH%\export.log ) else ( echo [%date% %time%] OK - %%T %EXPORT_PATH%\export.log ) ) endlocal這段腳本有幾個(gè)關(guān)鍵點(diǎn)。setlocal enabledelayedexpansion必須打開(kāi)因?yàn)閑rrorlevel在循環(huán)里要用!errorlevel!而不是%errorlevel%讀取否則每次取到的都是循環(huán)開(kāi)始前的舊值。if not exist負(fù)責(zé)建目錄忘了建目錄會(huì)導(dǎo)致 bcp 報(bào)“無(wú)法打開(kāi)文件”。每個(gè)表錯(cuò)誤時(shí)只記錄日志而不中斷整個(gè)循環(huán)這樣一張表失敗不會(huì)拖垮整批任務(wù)。3.3 配合導(dǎo)入建表、清表、BULK INSERT 的順序不能亂導(dǎo)入前先確認(rèn)目標(biāo)庫(kù)表結(jié)構(gòu)已經(jīng)存在。腳本包通常會(huì)在導(dǎo)出階段順手生成一份建表腳本或者用 bcp format 命令生成格式文件作為建表參考。導(dǎo)入的順序是先 TRUNCATE 舊數(shù)據(jù)再執(zhí)行 BULK INSERT最后重建索引。echo off setlocal set SERVER192.168.1.20,1433 set USERbackup_user set PASSYourPassword set DBMyDB set DATA_PATHD:\db_backup sqlcmd -S %SERVER% -U %USER% -P %PASS% -d %DB% -i sql\truncate_tables.sql if errorlevel 1 goto :end sqlcmd -S %SERVER% -U %USER% -P %PASS% -d %DB% -i sql\import_tables.sql if errorlevel 1 goto :end sqlcmd -S %SERVER% -U %USER% -P %PASS% -d %DB% -i sql\rebuild_index.sql if errorlevel 1 goto :end echo [%date% %time%] import success goto :end :end endlocaltruncate_tables.sql里寫(xiě)的是對(duì)目標(biāo)表執(zhí)行TRUNCATE TABLE目的是讓導(dǎo)入可重復(fù)執(zhí)行。這里有一個(gè)順序問(wèn)題必須先清表再導(dǎo)入否則 BULK INSERT 會(huì)往已有數(shù)據(jù)后面追加第二次運(yùn)行時(shí)數(shù)據(jù)翻倍。import_tables.sql的內(nèi)容就是上一章展示的 BULK INSERT 語(yǔ)句每張表一段建議把TABLOCK打開(kāi)等全部導(dǎo)入完成后再重建索引比導(dǎo)入前保留索引快很多。3.4 校驗(yàn)環(huán)節(jié)行數(shù)、聚合值與樣本比對(duì)數(shù)據(jù)導(dǎo)完不等于遷完必須驗(yàn)證。最基礎(chǔ)的是行數(shù)比對(duì)源庫(kù)和目標(biāo)庫(kù)各跑一次 COUNT_BIG人工或腳本比對(duì)結(jié)果。更穩(wěn)的校驗(yàn)是同時(shí)比對(duì)主鍵的聚合校驗(yàn)和與行數(shù)這樣能發(fā)現(xiàn)同構(gòu)但數(shù)據(jù)錯(cuò)位的極端情況。-- 源庫(kù)執(zhí)行 SELECT COUNT_BIG(1) AS RowCnt, SUM(CAST(CHECKSUM([ID], [Name], [Amount], [CreateTime]) AS BIGINT)) AS AggKey FROM [MyDB].[dbo].[Orders]; -- 目標(biāo)庫(kù)執(zhí)行結(jié)果應(yīng)與源庫(kù)完全一致 SELECT COUNT_BIG(1) AS RowCnt, SUM(CAST(CHECKSUM([ID], [Name], [Amount], [CreateTime]) AS BIGINT)) AS AggKey FROM [MyDB].[dbo].[Orders];CHECKSUM聚合校驗(yàn)的核心是把每一行變成一個(gè)整數(shù)再對(duì)全表求和。只要有一行數(shù)據(jù)的某個(gè)字段不一致總和幾乎必然變化。要注意CHECKSUM的結(jié)果范圍是 int 有符號(hào)整數(shù)直接求和可能溢出所以要包一層CAST(... AS BIGINT)。如果兩張表的這兩條結(jié)果完全一致基本可以斷定數(shù)據(jù)遷移成功。4. 三個(gè)必調(diào)參數(shù)組規(guī)模、編碼、權(quán)限腳本能不能跑通一半看參數(shù)調(diào)得對(duì)不對(duì)。這一章講腳本包里最值得花時(shí)間的三個(gè)參數(shù)組。4.1 批大小與錯(cuò)誤上限把大事務(wù)拆小把失敗控制在文件里bcp 的-b和 BULK INSERT 的BATCHSIZE是同一個(gè)概念每 N 行提交一個(gè)事務(wù)。批越小單次事務(wù)占用的日志越少失敗后回滾的成本越低批越大導(dǎo)入吞吐越高但一旦中間失敗回滾范圍也越大。我的實(shí)踐經(jīng)驗(yàn)是1GB 以下的表用 1000 到 500010GB 以上的表用 10000 到 20000再往上收益就不明顯了。-m錯(cuò)誤上限要跟批大小聯(lián)動(dòng)-m 100配合-b 5000意味著最多只能容忍 100 行壞數(shù)據(jù)超過(guò)立刻終止防止錯(cuò)誤記錄把文件撐爆。如果你導(dǎo)出的數(shù)據(jù)來(lái)自業(yè)務(wù)庫(kù)建議把-m設(shè)為 0也就是一個(gè)錯(cuò)誤都不允許因?yàn)闃I(yè)務(wù)庫(kù)的數(shù)據(jù)理論上不該有壞行出現(xiàn)任何一條都說(shuō)明上游有問(wèn)題。e錯(cuò)誤文件會(huì)記錄行號(hào)、列號(hào)和錯(cuò)誤原因格式如下這是排查時(shí)最直接的入口。第 100 行列 3無(wú)法將值 abc-123 轉(zhuǎn)換為 bigint 第 105 行列 7字符串或二進(jìn)制數(shù)據(jù)將被截?cái)?.2 字符模式、Unicode 與格式文件亂碼和錯(cuò)列的根源bcp 的-c是字符模式數(shù)據(jù)按數(shù)據(jù)庫(kù)排序規(guī)則轉(zhuǎn)成字符串輸出-w是 Unicode 模式輸出 UTF-16 文件-C 65001指定使用 UTF-8 代碼頁(yè)。三者選錯(cuò)輕則中文變問(wèn)號(hào)重則直接導(dǎo)入失敗。導(dǎo)出參數(shù)文件編碼適用場(chǎng)景-c隨系統(tǒng)代碼頁(yè)純 ASCII 或數(shù)字內(nèi)容-wUTF-16含 nvarchar / nchar 字段-c -C 65001UTF-8跨平臺(tái)交換、現(xiàn)代應(yīng)用如果表里含nvarchar字段我一般直接用-w不要貪圖-c的緊湊。字符模式下非 Unicode 類(lèi)型轉(zhuǎn)成 varchar中文內(nèi)容取決于目標(biāo)庫(kù)排序規(guī)則一旦目標(biāo)庫(kù)的代碼頁(yè)和源庫(kù)不一致必亂。-w雖然文件體積大一倍但能完全避開(kāi)編碼問(wèn)題。格式文件是另一個(gè)層面當(dāng)字段類(lèi)型需要精確指定、列順序需要調(diào)整時(shí)用bcp ... format nul -f schema.fmt -c先生成格式文件再做修改。4.3 權(quán)限模型與連接方式讓每個(gè)賬號(hào)只做一件事腳本包在正式環(huán)境跑權(quán)限不足是最常見(jiàn)的“第一夜翻車(chē)點(diǎn)”。bcp 導(dǎo)出需要源庫(kù)的 SELECT 權(quán)限BULK INSERT 需要目標(biāo)庫(kù)的 INSERT 權(quán)限同時(shí)要求服務(wù)器級(jí)別的ADMINISTER BULK OPERATIONS權(quán)限sqlcmd 執(zhí)行腳本需要對(duì)應(yīng)庫(kù)的 EXECUTE 權(quán)限。我建議拆分兩個(gè)賬號(hào)導(dǎo)出賬號(hào)只給db_datareader導(dǎo)入賬號(hào)單獨(dú)建只給目標(biāo)庫(kù)的db_datawriter加ADMINISTER BULK OPERATIONS。不要復(fù)用 sa 賬號(hào)否則腳本一旦出錯(cuò)影響面會(huì)擴(kuò)大到整個(gè)實(shí)例。連接方式上-S參數(shù)里顯式寫(xiě)端口號(hào)-U -P走 SQL Server 身份驗(yàn)證如果走 Windows 身份驗(yàn)證批處理里要用-E但任務(wù)計(jì)劃程序運(yùn)行時(shí)注意賬號(hào)上下文否則會(huì)報(bào)“用戶(hù) NT AUTHORITY\ANONYMOUS 登錄失敗”。5. 腳本導(dǎo)出導(dǎo)入避坑指南五個(gè)現(xiàn)場(chǎng)翻車(chē)記錄腳本方案最大的風(fēng)險(xiǎn)不在命令本身而在環(huán)境差異。這一章記錄五個(gè)我實(shí)際踩過(guò)的坑現(xiàn)象、原因、解決辦法按順序列出。5.1 文件找不到路徑職責(zé)與客戶(hù)端/服務(wù)端位置的混淆現(xiàn)象是 bcp 導(dǎo)出正常但 BULK INSERT 報(bào)“無(wú)法大容量加載文件 D:\db_backup\orders.dat 不存在”。原因在于路徑職責(zé)不同bcp 是客戶(hù)端工具讀取客戶(hù)端文件系統(tǒng)的路徑BULK INSERT 是服務(wù)端 T-SQL 語(yǔ)句讀取 SQL Server 服務(wù)進(jìn)程所在機(jī)器的路徑。如果你在本地執(zhí)行 BULK INSERT而 SQL Server 跑在另一臺(tái)服務(wù)器上D 盤(pán)路徑自然不存在。解決方法是把數(shù)據(jù)文件上傳到 SQL Server 所在機(jī)器或者用 UNC 共享路徑\\server\share\orders.dat并保證 SQL Server 服務(wù)賬號(hào)有共享目錄的讀取權(quán)限。反過(guò)來(lái)bcp in 模式則是從客戶(hù)端讀取文件推送給服務(wù)端邏輯正好相反。5.2 導(dǎo)入錯(cuò)列數(shù)據(jù)里的分隔符、引號(hào)與行終止符現(xiàn)象是導(dǎo)入后某些行多了一列或者某個(gè)字段變成 NULL。最常見(jiàn)的原因是數(shù)據(jù)內(nèi)容里本身包含豎線(xiàn)|導(dǎo)出的字段分隔符和內(nèi)容撞車(chē)。比如備注字段“說(shuō)明A|B|C”被拆成了三個(gè)字段后面的列全部錯(cuò)位。解決方法是換用數(shù)據(jù)中極少出現(xiàn)的控制字符比如\x01十六進(jìn)制 0x01。bcp 的-t |改成-t \x01BULK INSERT 的FIELDTERMINATOR 0x01?；蛘吒纱嘤酶袷轿募槊恳涣酗@式定義類(lèi)型和終止符格式文件可以完全避開(kāi)“讀出來(lái)再猜列”的問(wèn)題。5.3 中文變問(wèn)號(hào)代碼頁(yè)和排序規(guī)則的拉鋸戰(zhàn)現(xiàn)象是導(dǎo)出文件用記事本打開(kāi)正常導(dǎo)入后數(shù)據(jù)庫(kù)里的中文全部變成問(wèn)號(hào)。原因通常是字符模式-c下源庫(kù)的排序規(guī)則是 Chinese_PRC_CI_AS導(dǎo)出時(shí)按系統(tǒng) ANSI 代碼頁(yè)轉(zhuǎn)出目標(biāo)庫(kù)導(dǎo)入時(shí)又按自己的代碼頁(yè)解析中間至少做了一層有損轉(zhuǎn)換。解決方法是含中文或任何非 ASCII 字符的表導(dǎo)出時(shí)改用-wBULK INSERT 里對(duì)應(yīng)改成ROWTERMINATOR不變但不要寫(xiě)CODEPAGE。如果你堅(jiān)持用 UTF-8導(dǎo)出加-C 65001導(dǎo)入側(cè)寫(xiě)CODEPAGE 65001兩邊缺一不可。5.4 身份列沖突IDENTITY_INSERT 與 seed 重置現(xiàn)象是導(dǎo)入后目標(biāo)表的自增列值跟源表完全對(duì)不上或者導(dǎo)入時(shí)報(bào)“不能為表插入顯式值”。原因很直接源表有 IDENTITY 列bcp 導(dǎo)出時(shí)把自增值也導(dǎo)出來(lái)了但目標(biāo)表默認(rèn)不允許對(duì) IDENTITY 列做顯式插入。解決方法是導(dǎo)入前先SET IDENTITY_INSERT [表名] ON導(dǎo)入完成后執(zhí)行DBCC CHECKIDENT (表名, RESEED)重新校準(zhǔn)自增種子。腳本包的import_tables.sql里每張含 IDENTITY 列的表BULK INSERT 前后必須包這兩句否則二次導(dǎo)入時(shí)自增列會(huì)從錯(cuò)誤起點(diǎn)繼續(xù)跑。5.5 超時(shí)和中斷網(wǎng)絡(luò)包大小、防火墻與重跑策略現(xiàn)象是導(dǎo)出 1 小時(shí)左右任務(wù)卡住bcp 進(jìn)程還在但文件大小長(zhǎng)時(shí)間不變沒(méi)有任何報(bào)錯(cuò)。原因大多是網(wǎng)絡(luò)傳輸問(wèn)題或防火墻探測(cè)長(zhǎng)連接后靜默斷連。bcp 本身不提供精確的查詢(xún)超時(shí)參數(shù)但可以通過(guò)調(diào)節(jié)網(wǎng)絡(luò)包大小降低斷連概率同時(shí)讓腳本具備斷點(diǎn)重跑能力。bcp SELECT * FROM [MyDB].[dbo].[Orders] WHERE [CreateTime] 2024-01-01 queryout D:\db_backup\orders_2024.dat -S 192.168.1.10,1433 -U backup_user -P YourPassword -c -t | -r \n -b 5000 -a 4096-a 4096是網(wǎng)絡(luò)包大小默認(rèn)值偏保守調(diào)大后長(zhǎng)傳場(chǎng)景更穩(wěn)。同時(shí)給腳本加上“目標(biāo)文件已存在則跳過(guò)”或“按時(shí)間分片導(dǎo)出”的邏輯中斷后不用從頭跑。比如這個(gè)例子里的 WHERE 條件已經(jīng)按日期過(guò)濾重跑時(shí)把文件改成orders_2024_0901.dat只補(bǔ)跑缺失的區(qū)間即可。6. 把腳本包升級(jí)成無(wú)人值守的定時(shí)任務(wù)腳本跑通只是第一步真正讓這套方案有價(jià)值的是接進(jìn)任務(wù)計(jì)劃讓它每天安靜地完成導(dǎo)出導(dǎo)入。這里分享兩個(gè)我常用的技巧。6.1 日期變量與日志文件批處理里直接用%DATE%取日期很玄學(xué)不同系統(tǒng)區(qū)域設(shè)置會(huì)給出不同格式%DATE:~0,4%這種切割方式換個(gè)環(huán)境就翻車(chē)。我在生產(chǎn)環(huán)境的腳本包里統(tǒng)一改用 PowerShell 生成日期字符串再傳給批處理。$date Get-Date -Format yyyyMMdd $logDir D:\db_backup\logs if (-not (Test-Path $logDir)) { New-Item -ItemType Directory -Path $logDir | Out-Null } cmd /c export_all.bat | Out-File $logDir\export_$date.log -Encoding utf8 $exitCode $LASTEXITCODE exit $exitCode這段腳本把每天的導(dǎo)出日志按日期歸檔同時(shí)把批處理的退出碼原樣傳給任務(wù)計(jì)劃方便計(jì)劃程序把失敗任務(wù)標(biāo)注出來(lái)。日志文件只要保留最近 30 天的老文件用一個(gè)簡(jiǎn)單循環(huán)清理就行。6.2 任務(wù)計(jì)劃程序的調(diào)用與退出碼任務(wù)計(jì)劃程序里執(zhí)行 PowerShell 腳本時(shí)注意“起始于”目錄要設(shè)成腳本包所在目錄否則相對(duì)路徑全部失效。批處理里所有路徑都寫(xiě)成%~dp0開(kāi)頭是更穩(wěn)的做法這是指腳本自身所在目錄。set BASE_PATH%~dp0 set EXPORT_PATH%BASE_PATH%data set LOG_PATH%BASE_PATH%logs我吃過(guò)一次虧腳本在命令行手動(dòng)跑正常放到任務(wù)計(jì)劃里就報(bào)“系統(tǒng)找不到指定的路徑”后來(lái)發(fā)現(xiàn)是任務(wù)計(jì)劃的“起始于”留空了。從那以后我所有腳本包統(tǒng)一改成%~dp0拼絕對(duì)路徑再不依賴(lài)工作目錄。最后收個(gè)尾腳本導(dǎo)出導(dǎo)入這套玩法技術(shù)上并不復(fù)雜真正的價(jià)值在于把遷移過(guò)程變成可重復(fù)、可檢查、可自動(dòng)化的流水線(xiàn)。如果你最近也在為數(shù)據(jù)庫(kù)搬遷頭疼先拿一張小表跑通全流程再逐步放大希望幫到你。本文還有配套的精品資源點(diǎn)擊獲取