您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server BCP 匯出備份完整實戰：存儲過程自動化、參數詳解、中文亂碼解決

編輯：關於SqlServer

BCP (Bulk Copy Program) 是高效批量匯入/匯出工具，配合 xp_cmdshell 可在 T-SQL 中自動化備份指定表資料。

前置授權 (一次性)

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;

基本 BCP 指令結構

bcp "SELECT * FROM db.schema.table" queryout "D:\backup\table_20240101.txt" 
    -c -t, -S "Server\Instance" -U "user" -P "password"

關鍵參數速查

參數	說明	建議值
`queryout`	查詢結果導出	必填
`-c`	字元模式 (分隔符分隔)	通用匯出
`-w`	Unicode 原生模式	解決中文亂碼首選
`-t`	欄位分隔符	`-t,` (CSV) 或 `-t\t` (TSV)
`-r`	列分隔符	`-r\n`
`-S`	伺服器\實例	需與 SSMS 連線名一致
`-U/-P`	登入帳號/密碼	或用 `-T` 整合驗證
`-k`	保留 NULL (而非空字串)	視需求
`-b`	批次大小	匯入時建議 5000

自動化備份存儲過程 (按表名模式、保留 10 個月前資料)

CREATE PROCEDURE sp_Export_Backup_Tables
    @FilePath VARCHAR(200) = 'D:\SQLBackup\'
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @TableName SYSNAME, @SQL NVARCHAR(MAX), @Cmd NVARCHAR(4000), @FileName VARCHAR(200);

    DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
        SELECT name FROM sys.tables 
        WHERE name LIKE 'WM_ORDER%' OR name LIKE 'WM_PICKING%' OR name LIKE 'RP_%';

    OPEN cur;
    FETCH NEXT FROM cur INTO @TableName;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @FileName = @TableName + '_' + CONVERT(CHAR(8), GETDATE(), 112) + '.txt';
        SET @SQL = N'SELECT * FROM ' + QUOTENAME(DB_NAME()) + N'.dbo.' + QUOTENAME(@TableName) 
                   + N' WHERE DATEDIFF(MONTH, ModifyTime, GETDATE()) > 10';
        
        SET @Cmd = 'bcp "' + @SQL + '" queryout "' + @FilePath + @FileName + '" -w -T -S"' + @@SERVERNAME + '"';
        
        PRINT @Cmd; -- 除錯用
        EXEC master..xp_cmdshell @Cmd;
        
        FETCH NEXT FROM cur INTO @TableName;
    END
    CLOSE cur; DEALLOCATE cur;
END
GO

-- 執行
EXEC sp_Export_Backup_Tables 'D:\SQLBackup\';

常見錯誤與解決

錯誤代碼	原因	解決
SQLState=S1000, NativeError=0	資料庫/表名拼寫錯誤	檢查 QUOTENAME 包裹、結構描述正確
SQLState=S0002, NativeError=208	-S 伺服器名稱錯誤	用 `@@SERVERNAME` 或 SSMS 連線屬性確認
中文亂碼	預設 -c 使用代碼頁	改用 -w (Unicode)
存取被拒絕	輸出目錄權限不足	SQL Server 服務帳號需具寫入權限