使用 BCP 工具自動化 SQL Server 大量資料備份與還原

BCP 批次備份/還原腳本自動產生器

當資料表數量龐大，手寫 BCP 命令極為繁瑣。可利用系統目錄檢視快速產生批次檔，實現全庫資料的自動化匯出與匯入。

核心邏輯

查詢 sysobjects (類型 'U' 為使用者資料表)，動態拼接 BCP 命令字串：

SELECT 'bcp ' + DB_NAME() + '..' + name + ' out ' + 'D:\backup\' + name + '.out -n -S' + @@SERVERNAME + ' -Usa -Ppassword'
FROM sysobjects WHERE type = 'U'

操作步驟

1. 在 Query Analyzer (SQL 2000) 或 SSMS (SQL 2005+) 執行上述查詢。
2. 將結果複製到記事本 (勿複製欄位標題)，儲存為 backup.bat。
3. 在 .bat 檔同目錄下建立 Data 子目錄，備份檔將輸出於此。
4. 執行 backup.bat 即可完成全庫備份。

還原腳本產生

將查詢中的 out 改為 in，產生 restore.bat，即可批次將資料導回資料庫。

適用環境

• Sybase ASE / Microsoft SQL Server 7.0 / 2000 / 2005+
• 亦可在 PowerBuilder Database Administration 執行 (需補分號)，結果另存為文字檔後改副檔名為 .bat。

注意事項

• -n 參數使用原生資料類型格式，速度最快但僅限同版本 SQL Server 間移轉。
• 生產環境請避免在命令列明文暴露 sa 密碼，建議使用整合驗證 (-T) 或設定檔方式。
• 大量資料建議搭配 BULK INSERT 或 SSIS 取得更佳效能與錯誤處理。