前言

在管理大型SQL Server資料庫時,你是否遇過以下痛點？

  • 資料匯入匯出緩慢到讓人抓狂
  • 維護作業需要人工執行,常常加班到半夜
  • 大型資料庫備份還原總是出問題
  • 跨伺服器操作複雜又容易出錯

別擔心！本篇完整剖析SQLCMD的所有實戰技巧,讓你輕鬆應對各種資料庫維運挑戰。無論你是資深DBA還是新手工程師,都能從中學到實用的技巧！

SQLCMD 功能與效能概觀 SQLCMD 資料匯入匯出 批次處理優化 執行SQL指令 效能監控 資料庫備份 壓縮優化 自動化排程 錯誤處理 大型資料庫匯入效能比較 一般匯入 批次處理 最佳化設定

實戰經驗分享

案例一：10TB 大型資料庫的快速匯入

在某電力公司的專案中,我們需要匯入一個10TB的資料庫。使用傳統方式可能需要數天時間,但透過以下SQLCMD優化技巧,我們將時間縮短到12小時：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 設定最佳化參數
sqlcmd -S ServerName -U DBAdmin -P Password -d TargetDB -r 
-b                              -- 遇錯誤終止批次作業
-h-1                            -- 移除頁首
-k                              -- 保留格式化字元
-t 600                          -- 設定查詢逾時600秒
-i "D:\import_script.sql"       -- 指定匯入腳本

-- import_script.sql 內容
:setvar BATCHSIZE 10000         -- 設定批次大小
:setvar MAXDOP 4                -- 設定平行處理數

USE [TargetDB]
GO
SET NOCOUNT ON
GO
BEGIN TRANSACTION
    -- 在此加入您的資料匯入指令
    -- 建議每10000筆做一次COMMIT
COMMIT
GO

關鍵優化點：

  1. 適當的批次大小設定
  2. 平行處理數調整
  3. 記憶體使用最佳化
  4. 交易記錄管理

案例二：自動化備份策略

以下是一個實際運作中的自動化備份方案：

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
31
32
33
34
35
36
37
38
-- 建立排程備份腳本
-- backup_script.sql
:on error exit
:setvar LogPath "D:\Logs"
:setvar BackupPath "D:\Backups"

-- 錯誤處理與記錄
BEGIN TRY
    DECLARE @StartTime datetime = GETDATE()
    DECLARE @BackupFile nvarchar(500) = '$(BackupPath)\' + 
           DB_NAME() + '_' + 
           FORMAT(GETDATE(), 'yyyyMMdd_HHmmss') + '.bak'
    
    -- 執行完整備份
    BACKUP DATABASE [$(DatabaseName)]
    TO DISK = @BackupFile
    WITH COMPRESSION,                 -- 啟用壓縮
         CHECKSUM,                    -- 檢查備份資料完整性
         STATS = 10                   -- 顯示進度
    
    -- 記錄成功訊息
    DECLARE @EndTime datetime = GETDATE()
    DECLARE @Duration int = DATEDIFF(MINUTE, @StartTime, @EndTime)
    
    PRINT '備份完成：'
    PRINT '開始時間: ' + CONVERT(varchar, @StartTime, 120)
    PRINT '結束時間: ' + CONVERT(varchar, @EndTime, 120)
    PRINT '執行時間: ' + CAST(@Duration as varchar) + ' 分鐘'
END TRY
BEGIN CATCH
    -- 錯誤處理
    DECLARE @ErrorMessage nvarchar(4000) = ERROR_MESSAGE()
    DECLARE @ErrorSeverity int = ERROR_SEVERITY()
    
    -- 寫入錯誤記錄
    PRINT '備份失敗：' + @ErrorMessage
    RAISERROR(@ErrorMessage, @ErrorSeverity, 1)
END CATCH

進階效能優化技巧

1. 記憶體最佳化

1
2
-- 設定最佳記憶體使用量
sqlcmd -S ServerName -b -m 65535 -U DBAdmin -P Password

記憶體參數說明：

  • -m 設定最大記憶體使用量
  • 建議值：可用實體記憶體的 75%
  • 需考慮同時執行的其他處理程序

2. 平行處理優化

1
2
3
4
5
6
7
-- 設定平行處理
:setvar MAXDOP 4              -- 設定平行處理數
GO
sp_configure 'max degree of parallelism', $(MAXDOP)
GO
RECONFIGURE WITH OVERRIDE
GO

平行處理建議：

  • CPU核心數 ≤ 4：使用全部核心
  • 4 < CPU核心數 ≤ 8：使用核心數的75%
  • CPU核心數 > 8：使用8個核心

3. 分割檔案匯入策略

大型檔案建議分割處理：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 分割檔案匯入腳本
:on error exit
:setvar ChunkSize 1000000    -- 每個分割檔案的資料筆數

-- 讀取分割檔案
WHILE EXISTS (SELECT 1 FROM ##TempFileList WHERE Processed = 0)
BEGIN
    -- 處理單一分割檔案
    :r "$(CurrentChunkFile)"
    
    -- 更新處理狀態
    UPDATE ##TempFileList 
    SET Processed = 1
    WHERE FileName = '$(CurrentChunkFile)'
END

安全性強化設定

1. 加密傳輸設定

1
2
-- 啟用加密傳輸
sqlcmd -S ServerName -U DBAdmin -P Password -N                -- 強制加密

2. 稽核記錄設定

1
2
3
4
5
6
7
8
9
10
11
-- 啟用稽核記錄
CREATE SERVER AUDIT SQLCMDExecAudit
TO FILE (FILEPATH = 'D:\Audit\')

-- 設定稽核規格
CREATE SERVER AUDIT SPECIFICATION SQLCMDExecSpec
FOR SERVER AUDIT SQLCMDExecAudit
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (DATABASE_CHANGE_GROUP)
WITH (STATE = ON)

常見問題診斷與解決

  1. 連線逾時

    1
    2
    -- 增加連線逾時設定
    sqlcmd -S ServerName -U DBAdmin -P Password -t 600           -- 設定600秒逾時

  2. 記憶體不足

    1
    2
    -- 設定最大記憶體使用量
    sqlcmd -S ServerName -U DBAdmin -P Password -m 65535

  3. 權限問題

    1
    2
    3
    -- 檢查與設定必要權限
    GRANT CONTROL SERVER TO [DBAdmin]                            -- 設定伺服器層級權限
    GRANT CONTROL ON DATABASE::[TargetDB] TO [DBAdmin]          -- 設定資料庫層級權限

結論

透過本文介紹的SQLCMD進階技巧,你現在應該能夠：

  • 大幅提升資料匯入匯出效能
  • 建立穩定的自動化維護機制
  • 處理各種大型資料庫維運挑戰
  • 優化執行效能
  • 確保作業安全性

善用這些技巧,讓你的資料庫維運工作更輕鬆、更有效率！

參考資源