bcp公用程式

bulk copy program(bcp)適用於快速備份SQL Server
配合bulk insert可在幾分鐘的時間內傳送海量的資料
bcp可藉由安裝SSMS或下載獨立安裝程式取得

---

從本地資料庫匯出

以本地SQLExpress為例
匯出到系統 D 槽

原生格式匯出

匯出指令

bcp {資料庫名}.{資料表名} out D:\{檔名}.bcp -T -n -S .\SQLEXPRESS

原生格式匯入

bcp命令

本地

bcp {資料庫名}.{資料表名} in D:\{檔名}.bcp -T -n -S .\SQLEXPRESS

遠端

bcp {資料庫名}.{資料表名} in D:\{檔名}.bcp -n -S”ip位址” -U”帳號” -P”密碼”

特殊參數說明

參數 -T 代表使用windows認證
參數 -U”帳號” -P”密碼” 代表使用tcp連線

SQL 命令

SQL無法由本地匯到遠端伺服器,有此需求需使用上述bcp命令

BULK INSERT {資料表名}
    FROM 'D:\{檔名}.bcp'
    WITH
(
    BATCHSIZE = 1000,
    DATAFILETYPE = 'native',
    TABLOCK
);

---

字元格式匯出

備份指令

bcp {資料庫名}.{資料表名} out D:\{檔名}.txt -T -c -S .\SQLEXPRESS

字元格式匯入

bcp命令

本地

bcp {資料庫名}.{資料表名} in D:\{檔名}.txt -T -c -S .\SQLEXPRESS

遠端

bcp {資料庫名}.{資料表名} in D:\{檔名}.txtx -c -S”ip位址” -U”帳號” -P”密碼”

SQL 命令

BULK INSERT {資料表名}
    FROM 'D:\{檔名}.txt'
    WITH
(
    BATCHSIZE = 1000,
    FIELDTERMINATOR = '\t',
    ROWTERMINATOR = '\n',
    TABLOCK
);

---

如果你是要管資料庫的可憐碼農

上述指令只能備份和還原一個一個的資料表
如果要備份整個資料庫實在沒什麼卵用
資提供一個python範例檔做參考

批次匯出

# coding=utf-8
import os

#別自己傻傻的建
#SELECT ( '"' + name + '",') FROM sys.tables;
#可得到系統所有Table
#再copy paste前後加個 [] 即可
tables = ["TableName_1",
    "TableName_1",
    "TableName_2",
    "TableName_3",
    "TableName_4",
    "TableName_5",
    "TableName_6",]



#原生格式匯出命令

def bcp_cmds():
    bcp_cmd = "bcp {這裡放你的資料庫名稱}.%(table)s out D:\%(table)s.bcp -T -n -S .\SQLEXPRESS"
    return [ bcp_cmd % {"table": table} for table in tables ]

#開始備份所有table,這個語法實在離經叛道呀.....
[ os.system(cmd) for cmd in bcp_cmds() ]

批次匯入

# coding=utf-8
# 我相信身為碼農的你,會把tables獨立到一個獨立的模組
from bcp_tables import tables
import os

def bcp_host_cmds():
    bcp_insert_cmd = "bcp {這裡放你的資料庫名稱}.%(table)s in D:\%(table)s.bcp -T -n -S .\SQLEXPRESS"
    return [ bcp_insert_cmd % {"table": table } for table in tables ]

def bcp_remote_cmds():
    bcp_insert_cmd = 'bcp {這裡放你的資料庫名稱}.%(table)s in D:\%(table)s.bcp -n -S"伺服器IP" -U"帳號" -P"密碼"'
    return [ bcp_insert_cmd % {"table": table } for table in tables ]

#本地匯入
[ os.system(cmd) for cmd in bcp_host_cmds() ]

#遠端匯入
[ os.system(cmd) for cmd in bcp_remote_cmds() ]

產生 SQL 還原命令

產生完貼到SSMS就好

# coding=utf-8
# 我相信身為碼農的你,會把tables獨立到一個獨立的模組
from bcp_tables import tables
import os

bulk_insert_cmd = """
BULK INSERT %(table)s
FROM 'D:\%(table)s.bcp'
WITH
(
    BATCHSIZE = 1000,
    DATAFILETYPE = 'native',
    TABLOCK
);
"""


txt_sql_cmds = [ bulk_insert_cmd % {"table": table } for table in tables ]
for cmd in txt_sql_cmds:
    print cmd

python.exe generate_sql_cmds.py > bulk.script