前言

大型 SQL Server 環境裡，資料匯入匯出緩慢、維護作業得人工拖到半夜、備份還原偶爾出包、跨伺服器操作一多就容易漏步驟，這些幾乎是每個 DBA 遲早會遇到的問題。

sqlcmd 是 SQL Server 內建的命令列工具，把這些操作腳本化、掛上排程，能省下大量重複的手動工作。這篇整理我實際用過的幾種場景，並且特別把網路教學裡常見的幾個錯誤參數說明糾正過來——尤其是 -m 和 -k，被講錯的機率高得驚人。

SQLCMD 功能與效能概觀 SQLCMD 資料匯入匯出 批次處理優化 執行SQL指令 效能監控 資料庫備份 壓縮優化 自動化排程 錯誤處理 大型資料庫匯入耗時比較（示意，單位：分鐘） 逐筆 INSERT 批次處理 最小記錄載入

先搞清楚：命令列參數 vs T-SQL 腳本

很多人把 sqlcmd 學壞，是因為一開始就把兩件事混在一起。sqlcmd 後面接的東西分兩層：

  • 命令列參數（-S、-U、-i 這些）：寫在 shell（cmd / PowerShell / bash）裡，控制 sqlcmd 怎麼連線、怎麼跑。
  • T-SQL 與 sqlcmd 指令（:setvar、:r、BACKUP DATABASE）：寫在 .sql 腳本檔裡，由 sqlcmd 讀進去送給資料庫引擎執行。

這兩層用的註解語法不一樣：shell 裡 -- 不是註解（cmd 會把它當參數），T-SQL 裡 -- 才是。所以下面命令列我用 shell 註解（:: 或文字說明），腳本內容才用 --。混在同一行貼上去，多半跑不動。

大型資料庫匯入

匯入大量資料時，逐筆 INSERT 是最慢的選擇，每一筆都要寫完整交易記錄。真正能把時間從「數天」壓到「數小時」的關鍵，是用 BULK INSERT（或 bcp）搭配最小記錄載入。

先把匯入邏輯寫進腳本檔：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- import_script.sql
:on error exit
:setvar TargetDB "TargetDB"
:setvar DataFile "D:\export\bigtable.dat"

USE [$(TargetDB)];
GO
SET NOCOUNT ON;
GO

-- 最小記錄載入條件見下方說明，這裡只列必要的 WITH 選項
BULK INSERT dbo.BigTable
FROM '$(DataFile)'
WITH (
    DATAFILETYPE   = 'native',   -- 用 bcp -n 匯出的原生格式
    BATCHSIZE      = 100000,      -- 每 10 萬筆 commit 一次，控制交易記錄成長
    TABLOCK,                      -- 表級鎖，最小記錄載入的必要條件
    CHECK_CONSTRAINTS = OFF,      -- 載入時先停約束檢查，事後再驗
    KEEPNULLS
);
GO

最小記錄載入（minimally logged import）要同時滿足四個條件才能完整生效：①資料庫復原模式為 SIMPLE 或 BULK_LOGGED；②BULK INSERT 加上 TABLOCK（表級鎖）；③目標表是 heap，或是空的 clustered index——若 clustered index 非空，只有資料頁（data page）能最小記錄，索引頁仍會完整記錄；④目標表沒有啟用異動複寫（replication 啟用時無論復原模式都強制全量記錄）。漏掉任何一個，交易記錄就會回到完整寫入，磁碟 I/O 和記錄檔成長都會跟著放大。

再用命令列把這支腳本跑起來：

1
2
:: 在 cmd / PowerShell 執行（同一行，不要換行）
sqlcmd -S ServerName -U DBAdmin -P Password -b -t 600 -i "D:\import_script.sql"

命令列參數說明：

  • -b：遇到嚴重性大於 10 的錯誤時，sqlcmd 結束並回傳非 0 的 ERRORLEVEL，方便排程判斷成敗。
  • -t 600：查詢逾時 600 秒（不是登入逾時，登入逾時是 -l）。
  • -i：指定要執行的腳本檔。

能把 10TB 級資料壓進可接受的時間窗，靠的是這幾件事一起做：

  1. 載入前先把資料庫切到 SIMPLE 或 BULK_LOGGED 復原模式，搭配 TABLOCK；目標表若是 heap 或空的 clustered index，且沒有啟用異動複寫，才能走完整的最小記錄載入。
  2. 載入前先停掉目標表的非叢集索引，全部資料進去後再一次重建，比邊插邊維護索引快得多。
  3. 用 BATCHSIZE 控制每批 commit 的筆數，避免交易記錄一次撐爆。
  4. 來源資料切成多個檔，分多個 sqlcmd 工作平行載入（前提是表上有檔案群組或分割表能吃並行寫入）。

注意一點：:setvar 只是宣告 sqlcmd 腳本變數，宣告完之後一定要在後面用 $(變數名) 引用才有作用。光寫 :setvar BATCHSIZE 10000 而下面從沒用到，那行就是死碼，什麼都沒設定。

如果變數值需要從排程外部動態傳入（例如每次跑不同的日期或路徑），不用改腳本，改用命令列的 -v 參數覆蓋：

1
2
:: 從命令列傳入變數，腳本裡用 $(DataFile) 引用即可
sqlcmd -S ServerName -U DBAdmin -P Password -b -t 600 -i "D:\import_script.sql" -v DataFile="D:\export\20240101.dat"

-v 和 :setvar 效果相同，差別在於 -v 讓腳本可以保持不動，只靠呼叫端傳值——適合把同一支腳本掛在排程器上、每次帶不同參數跑。

自動化備份

把備份寫成腳本掛排程，是減少半夜加班的第一步。下面這支可以直接跑，關鍵是腳本內用到的每個變數都要先 :setvar 定義，否則在 :on error exit 下遇到未定義變數會直接中止。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- backup_script.sql
:on error exit
:setvar BackupPath "D:\Backups"
:setvar DatabaseName "TargetDB"

USE [master];
GO

BEGIN TRY
    DECLARE @StartTime datetime = GETDATE();
    DECLARE @BackupFile nvarchar(500) =
        N'$(BackupPath)\' + N'$(DatabaseName)' + N'_' +
        FORMAT(GETDATE(), 'yyyyMMdd_HHmmss') + N'.bak';

    BACKUP DATABASE [$(DatabaseName)]
    TO DISK = @BackupFile
    WITH COMPRESSION,   -- 啟用備份壓縮
         CHECKSUM,      -- 計算並驗證備份頁的總和檢查碼
         STATS = 10;    -- 每完成 10% 顯示一次進度

    DECLARE @Duration int = DATEDIFF(SECOND, @StartTime, GETDATE());
    PRINT N'備份完成：' + @BackupFile;
    PRINT N'耗時：' + CAST(@Duration AS varchar) + N' 秒';
END TRY
BEGIN CATCH
    -- 直接重拋原始錯誤，保留原本的錯誤號、訊息與狀態
    -- 比起用 RAISERROR 把 ERROR_MESSAGE() 重組再丟，THROW; 不會遺失原始錯誤資訊
    THROW;
END CATCH
GO

這裡刻意用 THROW;（不帶參數）而不是 RAISERROR(@msg, @severity, 1)。原因有兩個：

  • RAISERROR 重拋時得自己用 ERROR_MESSAGE()、ERROR_SEVERITY() 把錯誤重組，過程中原始錯誤號會遺失。
  • 大型備份失敗常丟出嚴重性 19 以上的錯誤，而 RAISERROR 要丟 19–25 的訊息必須加 WITH LOG 且要 sysadmin 權限，否則重拋這一步自己又會報錯，反而蓋掉真正的原因。

THROW; 在 CATCH 區塊裡會原封不動把原始錯誤丟回呼叫端，是新版（SQL Server 2012 起）官方建議的寫法。

進階效能調校

平行處理（MAXDOP）

MAXDOP（max degree of parallelism）控制單一查詢能用幾個邏輯處理器。它是伺服器層級設定，用 sp_configure 設定，跟 sqlcmd 命令列無關：

1
2
3
4
5
6
7
8
9
-- 把腳本變數帶進 sp_configure
:setvar MAXDOP 4

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'max degree of parallelism', $(MAXDOP);
RECONFIGURE;
GO

MAXDOP 該設多少，沒有「核心數超過 8 就一律設 8」這種死規則。Microsoft 的建議是依單一 NUMA 節點內的邏輯處理器數來決定：節點內處理器不超過 8 個時設成等於該數值，超過時以 8 為起點再依工作負載調整。SQL Server 2019 的安裝精靈（Database Engine Configuration 頁面新增的 MAXDOP 頁籤）會依當前硬體計算出建議值並顯示在 UI，讓你在安裝時接受或手動修改——這是安裝時的互動式提示，不是裝完後自動套用的設定，裝好後仍需透過 sp_configure 或 ALTER DATABASE SCOPED CONFIGURATION 自行調整。在大型 NUMA 機器上盲套「8」反而可能設錯，實務上要看自己機器的 NUMA 配置與工作負載類型（OLTP 通常設低、批次/報表可放寬）。

伺服器記憶體

這裡要特別澄清一個網路上流傳很廣的錯誤：sqlcmd 沒有任何控制伺服器記憶體的命令列參數。

很多教學把 sqlcmd -m 65535 講成「設定最大記憶體使用量」，這是完全錯的。-m 是錯誤訊息嚴重性等級開關：只有嚴重性大於等於該值的訊息才會送到輸出。SQL Server 錯誤嚴重性從 1 到 25，-m 另有一個特殊值 -m-1，帶它時連資訊訊息也一起顯示；不帶 -m 或帶 -m 0 時預設只送出嚴重性 11 以上的訊息。

關於 -m-1 的寫法，在 cmd 環境下 -m -1（加空格）是不合法語法，必須連寫成 -m-1。PowerShell 行為略有不同：PowerShell 在解析命令列參數時，-1 開頭的字串可能被視為獨立引數而非 -m 的值，導致 sqlcmd 拿到的 -m 無值或解析異常；保險起見在 PowerShell 裡也統一連寫 -m-1，不要依賴空格分隔。

65535 早已超出錯誤嚴重性上限，就算引擎不報錯，效果也只是把訊息門檻拉到天花板——換句話說，幾乎所有訊息都被你關掉了，跟記憶體一點關係都沒有。

要設記憶體上限，是用 sp_configure 'max server memory'：

1
2
3
4
5
6
7
-- 設定 SQL Server 最大記憶體（單位：MB），這才是真正在管記憶體
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'max server memory (MB)', 49152;   -- 例：48 GB
RECONFIGURE;
GO

建議值通常是「實體記憶體扣掉作業系統與其他服務所需」之後留給 SQL Server，常見抓 75%–90% 視機器專用程度而定，要保留足夠記憶體給 OS。

分割檔案匯入

來源檔太大時，先在外部切成多個檔，再用 sqlcmd 的 :r 指令逐一帶進主腳本。注意 :r 是把檔案內容內嵌進當前腳本，不是迴圈讀檔：

1
2
3
4
5
-- 主匯入腳本：依序帶入每個分割檔
:on error exit
:r "D:\chunks\part_001.sql"
:r "D:\chunks\part_002.sql"
:r "D:\chunks\part_003.sql"

如果分割檔數量是動態的，比較實際的做法是在 shell（PowerShell / 批次檔）那一層用迴圈，把每個檔丟給一次 sqlcmd 呼叫，而不是想在單一 .sql 裡用 T-SQL 的 WHILE 去讀外部檔案——T-SQL 本身沒有直接列舉磁碟檔案的標準方法。

安全性設定

驗證方式

範例裡一直用 -U DBAdmin -P Password 的 SQL 驗證，但在有 Active Directory 的企業環境，Windows 整合驗證（-E）才是主流且更安全的選項：

1
2
:: Windows 整合驗證，不需要帳號密碼參數
sqlcmd -S ServerName -E -i backup_script.sql

-E 用目前執行 sqlcmd 的 Windows 使用者身分登入，帳密不出現在腳本或排程任務裡，也不怕被人在 Process List 裡看到明文密碼。沒有 AD 或需要連跨網域的伺服器才用 SQL 驗證。

加密連線

-N 要求加密連線。在較新版本的 sqlcmd（基於 ODBC 的 18 版以後）單獨用 -N 而不加 -C 時，會驗證伺服器憑證：

1
2
3
4
5
:: 強制加密並驗證伺服器憑證（SQL 驗證）
sqlcmd -S ServerName -U DBAdmin -P Password -N

:: 強制加密並驗證伺服器憑證（Windows 整合驗證）
sqlcmd -S ServerName -E -N

如果伺服器用的是自簽憑證、客戶端又沒匯入信任，光 -N 會連不上並報憑證錯誤。測試環境若要在自簽憑證下仍加密但不驗證，可加 -C（信任伺服器憑證）——但正式環境不該這樣放行，應該佈署受信任的憑證。

稽核記錄

要記錄登入與資料庫變更，需要建立伺服器稽核（Server Audit）與稽核規格（Audit Specification）。這裡有個容易踩的坑：CREATE SERVER AUDIT 建立出來預設是停用狀態，必須額外用 ALTER SERVER AUDIT ... WITH (STATE = ON) 才會真的開始寫記錄：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 1. 建立伺服器稽核（此時為停用狀態）
CREATE SERVER AUDIT SQLCMDExecAudit
TO FILE (FILEPATH = 'D:\Audit\');
GO

-- 2. 建立稽核規格並啟用
CREATE SERVER AUDIT SPECIFICATION SQLCMDExecSpec
FOR SERVER AUDIT SQLCMDExecAudit
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (DATABASE_CHANGE_GROUP)
WITH (STATE = ON);
GO

-- 3. 關鍵步驟：啟用稽核本體，少了這步整套稽核不會寫任何記錄
ALTER SERVER AUDIT SQLCMDExecAudit
WITH (STATE = ON);
GO

只在 SPECIFICATION 上加 STATE = ON、卻忘了開 audit 本體，是很常見的設定失誤——介面上看起來建好了，實際一條記錄都沒進檔案。

常見問題

1. 連線逾時

分清楚兩種逾時：-l 是登入逾時，-t 是查詢逾時。連線階段卡住要調 -l：

1
sqlcmd -S ServerName -U DBAdmin -P Password -l 60 -t 600

2. 想看完整錯誤輸出

把錯誤訊息導到 stderr 用 -r，但它要明確帶 0 或 1：-r0 只導嚴重性 11 以上的錯誤，-r1 連 PRINT 都導。單寫一個裸 -r 是不合法的：

1
2
:: 把 stdout 與 stderr 分開存檔
sqlcmd -S ServerName -U DBAdmin -P Password -i run.sql -r1 1> out.log 2> err.log

3. 輸出裡有多餘的控制字元

-k 是移除輸出中的控制字元（如 tab、換行），不是「保留」。-k（等同 -k1）移除、-k2 則把控制字元替換成空白。需要把結果餵給下游程式、不想被換行符搞亂時很好用：

1
sqlcmd -S ServerName -U DBAdmin -P Password -i query.sql -k1

4. 權限不足

依需要授予對應層級的權限，盡量別直接給 CONTROL SERVER 這種等同 sysadmin 的大權限：

1
2
3
4
5
-- 視實際需求授予最小權限，例如只給備份/還原
GRANT BACKUP DATABASE TO [DBAdmin];
GRANT BACKUP LOG TO [DBAdmin];
-- 確實需要時才在特定資料庫層級放行
GRANT CONTROL ON DATABASE::[TargetDB] TO [DBAdmin];

結論

sqlcmd 本身不複雜，難的是分清楚哪一層該寫什麼，以及哪些參數被網路教學講壞了。幾個最容易踩到的地方：大量匯入要靠 BULK INSERT + TABLOCK + 正確的復原模式，最小記錄載入的條件一個都不能少；-m 跟記憶體完全無關，它控制的是訊息嚴重性門檻，-m-1 要連寫，加空格在 cmd 就壞；-k 是移除控制字元，-r 要明確帶 0 或 1；CATCH 區塊重拋用 THROW; 而不是 RAISERROR；CREATE SERVER AUDIT 建好是停用狀態，少了 ALTER ... STATE = ON 那一行，稽核等於沒開。

把這些腳本掛上排程，半夜被叫起來手動跑作業的機會就少很多。

參考資源