BCPの使い方

Last-modified: 2015-12-29 (火) 00:17:49
amazon.gif

Amazon SQL本

SQLServerのバックアップについて

考えられる方法はおおまかに５つ

１.拡張子が　.ldf ・ .mdf の実データをバックアップ
　　テーブル・ログファイル・インデックスなどの全ての情報をバックアップ。ファイル単位で行う。

２.テーブルをテキストファイルとして出力（ログファイルなどは残らない）
　　テーブルの生データを出力する。ログファイル・インデックス情報などは出力しない。テキストデータ単位。

３.ManegementStudioExpressのバックアップ機能を利用
　　テーブル・ログファイル・インデックスなどの全ての情報をバックアップ。Expressより上位エディションではバックアップのタスクを組むことができる。

４.イメージバックアップを利用
　　パソコンやサーバのデータをOSのイメージごとバックアップする方法
「Symantec Backup Execシリーズ」や「Acronisシリーズ」など
　
Amazon バックアップ関連商品

５、バッチファイルにより、SQLCMDを実行しバックアップを行う(.bakファイル作成)
　　sqlcmdユーティリティ

 

ここでは２の、DOSコマンド(BCP)でテーブルをテキストとして出力する方法を紹介。

 
 
amazon.gif

　SQL SERVER 2005 Express Edition の場合。

データのエクスポート・インポート機能がManegementStudioExpressでは使えない（上位のエディションはある）らしく、バッチファイルでそれをすることになった。　

例文

データをエクスポート

Shop01のユーザがShopDBのHinDBテーブルをファイル出力する。

bcp [ShopDB].[dbo].[HinDB] out C:\BackUp\HinDB.dat -c -t "," -T -S Shop01\sqlexpress

データをインポート　（※　インポートする前にテーブルのデータが空でないととエラーがでる？）

Shop01のユーザがファイルからShopDBのHinDBテーブルに取り込む

bcp [ShopDB].[dbo].[HinDB] in C:\BackUp\HinDB.dat -c -t "," -T -S Shop01\sqlexpress

考察 その他

レコード数が多数（何万行以上）のテーブルを連続してインポートするとたまにこける(インポートされてない)時があるような･･･。
(例)
bcp [ShopDB].[dbo].[HinDB] in C:\BackUp\HinDB.dat -c -t "," -T -S Shop01\sqlexpress
bcp [ShopDB].[dbo].[SinDB] in C:\BackUp\SinDB.dat -c -t "," -T -S Shop01\sqlexpress

間に Pause をいれて。わざと時間差を作ってインポートすると解決しましたが。
(例)
bcp [ShopDB].[dbo].[HinDB] in C:\BackUp\HinDB.dat -c -t "," -T -S Shop01\sqlexpress
pause
bcp [ShopDB].[dbo].[SinDB] in C:\BackUp\SinDB.dat -c -t "," -T -S Shop01\sqlexpress
pause

皆さん同じような経験ありませんか？私の手違い・・・？？Expressを過信しすぎか？何万行のレコード数なので当たり前か？

 

ここから下記よりMicroSoftより引用(サイトが無くなった時のバックアップ用)

　SQL Server 2005 Express Edition からデータをエクスポートする方法

SQL Server 2005 Books Online (2007 年 9 月)
bcp ユーティリティ

更新 : 2007 年 2 月 1 日

bcp ユーティリティは、ユーザー指定の形式に基づいて、Microsoft SQL Server 2005 インスタンスとデータ ファイルとの間でデータの一括コピーを行います。
bcp ユーティリティを使用すると、多数の新規行を SQL Server テーブルにインポートしたり、データをテーブルからデータ ファイルにエクスポートすることができます。
このユーティリティでは Transact-SQL の知識は必要ありません。
ただし、queryout オプションと同時に使用する場合は、Transact-SQL の知識が必要になります。
データをテーブルにインポートするには、そのテーブル用に作成されたフォーマットファイルを使用するか、テーブルの構造およびテーブルの列に有効なデータの型を理解しておく必要があります。

トピック リンク アイコン bcp の構文規則の詳細については、「コマンド プロンプト ユーティリティ」を参照してください。

構文

bcp {[[database_name.][owner].]{table_name | view_name} | "query"}

   {in | out | queryout | format} data_file
   [-mmax_errors] [-fformat_file] [-x] [-eerr_file]
   [-Ffirst_row] [-Llast_row] [-bbatch_size]
   [-n] [-c] [-N] [-w] [-V (60 | 65 | 70 | 80)] [-6]
   [-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term]
   [-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]
   [-Sserver_name[\instance_name]] [-Ulogin_id] [-Ppassword]
   [-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]
引数

database_name

　指定したテーブルまたはビューを含むデータベースの名前を指定します。指定しない場合は、ユーザーの既定データベースになります。

owner

　テーブルまたはビューの所有者の名前を指定します。操作を実行するユーザーが指定のテーブルまたはビューを所有している場合、owner は省略可能です。owner が指定されず、
操作を実行するユーザーが指定のテーブルまたはビューを所有していない場合は、SQL Server 2005 からエラー メッセージが返され、操作は取り消されます。

table_name

　データを SQL Server にインポートする (in オプション) 場合はインポート先のテーブルの名前、
データを SQL Server からエクスポートする (out オプション) 場合はエクスポート元のテーブルの名前を指定します。

view_name

　データを SQL Server にコピーする (in オプション) 場合はコピー先のビューの名前、データを SQL Server からコピーする (out オプション) 場合はコピー元のビューの名前を指定します。
すべての列が同じテーブルを参照しているビューのみが、コピー先のビューとして使用できます。データをビューにコピーするときの制限の詳細については、「INSERT (Transact-SQL)」を参照してください。

" query "

　結果セットを返す Transact-SQL クエリです。
SELECT ステートメントで COMPUTE 句を指定した場合のように、クエリが複数の結果セットを返す場合、最初の結果セットだけがデータファイルにコピーされます。
以降の結果セットはコピーされません。クエリ全体を二重引用符で囲んでください。
クエリ内に組み込まれるものは、単一引用符で囲みます。クエリからデータを一括コピーする場合には、queryout も指定します。

in | out | queryout | format 

   次に示すように、一括コピーの方向を指定します。
 * in はファイルからデータベース テーブルまたはビューへのコピーを行います。
 メモ :
   SQL Server 6.5 の bcp ユーティリティの場合、sql_variant 型または bigint 型のデータを含むテーブルへの一括コピーはサポートされません。
 * out はデータベース テーブルまたはビューからファイルへのコピーを行います。

　bcp ユーティリティは、データを抽出するときに、空文字列を NULL 文字列に、NULL 文字列を空文字列で表すことに注意してください。

 * queryout はクエリからのコピーを行います。データをクエリから一括コピーする場合にのみ指定する必要があります。
 * format は、指定されたオプション (-n、-c、-w、-6、または -N)、およびテーブルやビューの区切り記号に基づいてフォーマット ファイルを作成します。

　データを一括コピーするとき、bcp コマンドはフォーマット ファイルを参照することができるため、フォーマット情報を対話的に再入力する必要がなくなります。
　format オプションには -f オプションが必要です。
XML フォーマット ファイルを作成する場合には、-x オプションも必要です。詳細については、「フォーマット ファイルの作成」を参照してください。

data_file 

   データ ファイルの完全パスを指定します。SQL Server にデータを一括インポートする場合は、データファイルには指定したテーブルまたはビューにコピーするデータが含まれます。

　　SQL Server からデータを一括エクスポートする場合は、データファイルにはテーブルまたはビューからコピーしたデータが含まれます。パスは、1 文字から 255 文字までです。
　　データファイルに含めることができる行の数は最大 2,147,483,647 です。

   重要 :
   format オプションを使用する場合、data_file (format nul) の値として nul を指定する必要があります。

-m max_errors 

   bcp 操作を取り消す前に発生することのできる構文エラーの最大数を指定します。構文エラーは、対象となるデータ型へのデータの変換エラーを意味しています。

　　max_errors の合計では、制約違反など、サーバーでのみ検出することができるエラーはすべて対象外となります。

   bcp ユーティリティでコピーできない行は無視され、エラーとしてカウントされます。このオプションが指定されない場合の既定値は 10 になります。
   メモ :
   -m オプションも、money データ型あるいは bigint データ型の変換には適用されません。

-f format_file 

   フォーマット ファイルの完全パスを指定します。このオプションの意味は、オプションが使用されている環境によって次のように異なります。
       * -f が format オプションと共に使用される場合は、指定されたテーブルまたはビューに対して、指定された format_file が作成されます。

　　　　XML フォーマット ファイルを作成するには、-x オプションも指定します。詳細については、「フォーマット ファイルの作成」を参照してください。

       * in または out オプションと共に使用される場合は、-f には既存のフォーマット ファイルが必要です。
         メモ :
         in オプションまたは out オプションでは、フォーマット ファイルは省略可能です。
  • f オプションを省略すると、-n、-c、-w、-6、または -N を指定しなかった場合に、
    コマンド プロンプトによってフォーマット情報が要求され、それに対する応答がフォーマット ファイルに保存されます (既定のファイル名は Bcp.fmt)。
  • x
   format オプションおよび -f format_file オプションと共に使用すると、XML ベースのフォーマット ファイルが生成されます。

　　このオプションを指定しないと、既定で XML 以外のフォーマット ファイルが生成されます。-x はデータのインポート時やエクスポート時には機能しません。
　　format および -f format_file の両方を指定せずに使用すると、エラーが生成されます。

   メモ :
   -x スイッチを使用するには、bcp 9.0 クライアントを使用する必要があります。bcp 9.0 クライアントの使用方法の詳細については、後の「解説」を参照してください。

-e err_file 

   bcp ユーティリティがファイルからデータベースに転送できなかった行を格納するエラー ファイルの完全パスを指定します。

　　bcp コマンドからのエラーメッセージは、ユーザーのワークステーションに送られます。このオプションを指定しないと、エラー ファイルは作成されません。

-F first_row 

   テーブルからエクスポートする最初の行、またはデータ ファイルからインポートする最初の行の番号を指定します。

　　このパラメータには、0 より大きく (> 0)、行の合計数以下 (< または =) となる値が必要です。このパラメータがない場合、既定ではファイルの最初の行となります。

-L last_row 

   テーブルからエクスポートする最後の行、またはデータ ファイルからインポートする最後の行の番号を指定します。

　　このパラメータには、0 より大きく (> 0)、最後の行の番号以下 (< または =) となる値が必要です。このパラメータがない場合、既定ではファイルの最後の行となります。

-b batch_size 

   一括インポートするデータの行数を指定します。コミットされる前に、各バッチはすべてのバッチをインポートする個別のトランザクションとしてインポートおよび記録されます。

　　既定では、データ ファイルのすべての行が 1 つのバッチとしてインポートされます。複数のバッチに行を分散するには、データファイルの行数よりも少ない batch_size を指定します。
　　バッチのトランザクションが失敗すると、現在のバッチの挿入のみがロールバックされます。
　　コミットされたトランザクションによって既にインポートされているバッチは、それ以降の失敗による影響を受けません。

   このオプションは、h"ROWS_PER_BATCH = bb" オプションと組み合わせて使用しないでください。
   詳細については、「一括インポートのバッチの管理」を参照してください。

-n 

   データのネイティブ (データベース) データ型を使用して一括コピー操作を実行します。

このオプションを使用すると、フィールドごとにプロンプトが表示されません。ネイティブ値が使用されます。

   詳細については、「ネイティブ形式を使用したデータのインポートまたはエクスポート」を参照してください。

-c 

   文字データ型を使用して操作を実行します。このオプションを使用すると、フィールドごとにプロンプトが表示されません。

char をプレフィクスなしのストレージ型として、また \t (タブ) をフィールド区切り文字、\r\n (改行文字) を行ターミネータとして使用します。

   詳細については、「文字形式を使用したデータのインポートまたはエクスポート」を参照してください。

-N 

   文字以外のデータについてはデータベースのネイティブなデータ型を使用し、文字データについては Unicode 文字を使用して、一括コピー操作を実行します。

　　-w オプションの代わりにこのオプションを使用すると、高いパフォーマンスが得られます。
このオプションは、データ ファイルを使用して SQL Server のインスタンスから別のインスタンスにデータを転送する場合に使用します。
　　フィールドごとにプロンプトは表示されません。パフォーマンスの高いネイティブモードを利用して ANSI の拡張文字を含むデータを転送する場合は、このオプションを使用します。
　　-N は SQL Server 6.5 またはそれ以前のバージョンでは使用できません。

   詳細については、「Unicode ネイティブ形式を使用したデータのインポートまたはエクスポート」を参照してください。

-w 

   Unicode 文字を使用して一括コピー操作を実行します。このオプションを使用すると、フィールドごとにプロンプトが表示されません。

　　ストレージ型 nchar、プレフィクスなし、フィールド区切り文字 \t (タブ)、行ターミネータ \n (改行文字) が使用されます。
　　このオプションは SQL Server 6.5 またはそれ以前のバージョンでは使用できません。

   詳細については、「Unicode 文字形式を使用したデータのインポートまたはエクスポート」を参照してください。

-V ( 60| 65| 70| 80) 

   以前のバージョンの SQL Server のデータ型を使用して一括コピー操作を実行します。

このオプションを使用すると、フィールドごとにプロンプトが表示されません。既定値が使用されます。
たとえば、ODBC ではサポートされなくなったが、SQL Server 6.5 の bcp ユーティリティでサポートされている日付形式を SQL Server 2005 に一括コピーするには、-V 65 パラメータを使用します。

   重要 :
   データを SQL Server からデータ ファイルに一括エクスポートする場合、-V が指定されていても、bcp ユーティリティでは、

SQL Server 6.0 または SQL Server 6.5 の datetime 型 (日付時刻型) や smalldatetime 型 (短精度日付時刻型) の日付形式は生成されません。
　　日付は常に ODBC の形式で書き込まれます。
さらに、SQL Server 6.5 およびそれ以前のバージョンでは NULL 値を許可する bit データがサポートされていないので、bit の列には NULL 値の代わりに 0 値が書き込まれます。

   詳細については、「以前のバージョンの SQL Server からのネイティブ形式データおよび文字形式データのインポート」を参照してください。

-6 

   SQL Server 6.0 または SQL Server 6.5 のデータ型を使用して一括コピー操作を実行します。

このオプションは、以前のバージョンとの互換性を保つためにのみサポートされています。
　　SQL Server 7 およびそれ以降の場合は、代わりに -V オプションを使用してください。

-q 

   bcp ユーティリティと SQL Server のインスタンスとの接続で、SET QUOTED_IDENTIFIERS ON ステートメントを実行します。

　　名前に空白や単一引用符が含まれるデータベース、所有者、テーブル、またはビューを指定する場合に、このオプションを使用します。
3 つの要素から成るテーブル名またはビュー名全体を、二重引用符 (" ") で囲みます。

   空白や単一引用符を含むデータベース名を指定するには、&#8211;q オプションを使用する必要があります。
   詳細については、後の「解説」を参照してください。

-C { ACP | OEM | RAW | code_page } 

   以前のバージョンの SQL Server との互換性を保つためにサポートされています。

SQL Server 7.0 以降の場合、Microsoft では、フォーマット ファイルの各列に対して照合順序名を指定することをお勧めします。

   データ ファイルにあるデータのコード ページを指定します。

文字コードが 127 を超えるかまたは 32 未満である文字を含む char、varchar、または text 列がデータに含まれる場合にのみ code_page は関係します。

   コード ページ値 	説明
   ACP
   ANSI/Microsoft Windows (ISO 1252) 用です。
   OEM
   クライアントが使用する既定のコード ページです。-C が指定されていない場合に使用される既定のコード ページです。
   RAW
   コード ページの変換は行われません。したがって、これは最も速いオプションです。
   code_page
   850 などの特定のコード ページ番号を指定します。
   詳細については、「異なる照合順序間でのデータのコピー」を参照してください。

-t field_term 

   フィールド ターミネータを指定します。既定値は、\t (タブ文字) です。

既定のフィールド ターミネータを無効にする場合、このパラメータを使用します。
詳細については、「フィールド ターミネータと行ターミネータの指定」を参照してください。

-r row_term 

   行ターミネータを指定します。既定値は、\n (改行文字) です。既定の行ターミネータを無効にする場合、このパラメータを使用します。

詳細については、「フィールド ターミネータと行ターミネータの指定」を参照してください。

-i input_file 

   応答ファイルの名前を指定します。応答ファイルには、対話モード (-n、-c、-w、-6、または -N が指定されていないモード) で一括コピーを実行する場合の、

各データ フィールドに関するコマンド プロンプトの質問への応答が含まれます。

-o output_file 

   コマンド プロンプトからリダイレクトされた出力を受け取るファイル名を指定します。

-a packet_size 

   サーバーとの間で送信されるネットワーク パケットごとのバイト数を指定します。

サーバー構成オプションは、SQL Server Management Studio または sp_configure システム ストアド プロシージャを使用して設定できます。
ただし、このオプションを使用すれば、サーバー構成オプションを個別に無効にできます。
packet_size の有効値は 4,096 ～ 65,535 バイトです。既定値は 4,096 です。

   パケット サイズを大きくすると、一括コピーのパフォーマンスを向上させることができます。

より大きなサイズのパケットを要求しても、許可されない場合、既定値が使用されます。
bcp ユーティリティが作成するパフォーマンス統計は、使用したパケット サイズを示します。

-S server_name[ \instance_name] 

   接続先となる SQL Server のインスタンスを指定します。

サーバーを指定しない場合、bcp ユーティリティは、ローカル コンピュータ上の SQL Server の既定のインスタンスに接続されます。
ネットワーク上のリモート コンピュータまたはローカルの名前付きインスタンスから bcp コマンドを実行するときは、このオプションが必要です。
サーバー上にある SQL Server の既定のインスタンスに接続するには、server_name のみを指定します。
SQL Server 2005 の名前付きインスタンスに接続するには、server_name\instance_name を指定します。

-U login_id

SQL Server への接続に使用されるログイン ID を指定します。

セキュリティ メモ :
　bcp ユーティリティが、統合セキュリティを使用した信頼関係接続で SQL Server に接続している場合、
user name および password の組み合わせではなく、-T オプション (信頼関係接続) を使用します。

-P password 

   ログイン ID のパスワードを指定します。このオプションを指定しない場合、bcp コマンドによってパスワードが要求されます。

また、このオプションをコマンド プロンプトの最後にパスワードなしで使用すると、bcp では既定のパスワード (NULL) が使用されます。

   セキュリティ メモ :
   空白のパスワードは使用しないでください。強力なパスワードを使用してください。
   パスワードをマスクする場合は、-P オプションを -U オプションと共には指定しないでください。

bcp を -U オプションおよび他のスイッチと共に指定した後 (-P は指定しない)、Enter キーを押すと、このコマンドによってパスワードが要求されます。
この方法を使用すると、入力時にパスワードが確実にマスクされます。

-T 

   bcp ユーティリティは、統合セキュリティを使用した信頼関係接続で SQL Server に接続します。

ネットワーク ユーザーのセキュリティ資格情報、login_id、および password は必要ありません。
–T を指定しない場合、正常にログオンするには –U や –P を指定する必要があります。

-v 

   bcp ユーティリティ バージョン番号と著作権に関する情報を報告します。

-R 

   通貨、日付、時刻のデータを SQL Server に一括コピーする場合に、クライアント コンピュータのロケール設定に定義された地域別設定が使用されます。

既定の設定では、地域別設定は無視されます。

-k 

   一括コピー操作時、空の列には、挿入される列の既定値ではなく、NULL 値が保持されます。

詳細については、「一括インポート中の NULL の保持または既定値の使用」を参照してください。

-E 

   ID 列には、インポートされたデータ ファイル内の ID 値が使用されます。
  • E を指定しない場合、インポートされるデータ ファイルの ID 列の値は無視され、
    SQL Server 2005 はテーブルの作成時に指定されたシードと増分の値に基づいて一意の値を自動的に割り当てます。
データ ファイルにテーブルまたはビュー内の ID 列の値が含まれない場合は、フォーマットファイルを使用して、データのインポート時にテーブルまたはビュー内の ID 列を無視するように指定します。

SQL Server 2005 では、一意な値が自動的にこの列に割り当てられます。詳細については、「DBCC CHECKIDENT (Transact-SQL)」を参照してください。

  • E オプションには、特別な権限が必要です。詳細については、後の「解説」を参照してください。 
     詳細については、「データの一括インポート時の ID 値の保持」に記載されている、ID 値の保持に関する説明を参照してください。

-h " hint[ ,... n] " 

   データをテーブルまたはビューに一括インポートするときに使用するヒントを指定します。

SQL Server 6.x またはそれ以前のバージョンにデータを一括コピーする場合は、このオプションは使用できません。

   ORDER(column [ASC | DESC] [,...n])

　データ ファイルのデータの並べ替え順序です。
インポートするデータをテーブル上のクラスタ化インデックス (存在する場合) に従って並べ替えると、一括インポートのパフォーマンスが向上します。
データファイルが異なる順序で並べ替えられている場合、つまり、クラスタ化インデックスキーの順序以外で並べ替えられている場合、
またはテーブルにクラスタ化インデックスが存在しない場合、ORDER 句は無視されます。
指定する列の名前は、インポート先のテーブル内で有効な列であることが必要です。
既定では、bcp はデータ ファイルの並べ替えが行われていないことを前提としています。
最適化された一括インポートでは、SQL Server でも、インポートされるデータが並べ替えられていることが検証されます。

       詳細については、「データの一括インポート時の並べ替え順の制御」を参照してください。
   ROWS_PER_BATCH = bb
       各バッチあたりのデータ行数 (bb) です。
  • b を指定しない場合に使用します。データ ファイル全体が 1 つのトランザクションとしてサーバーに送られます。
    サーバーは、bb の値に応じて一括読み込みを最適化します。ROWS_PER_BATCH の既定値はありません。
       詳細については、「一括インポートのバッチの管理」を参照してください。
   KILOBYTES_PER_BATCH = cc
       バッチごとのデータの概算キロバイト数 (KB) です (cc)。KILOBYTES_PER_BATCH の既定値はありません。
       詳細については、「一括インポートのバッチの管理」を参照してください。
   TABLOCK
       一括読み込み操作中に一括更新のテーブルレベルのロックが適用されます。これを指定しない場合、行レベルのロックが適用されます。

一括コピー操作時だけロックすることにより、テーブル ロックの競合が少なくなるので、このヒントはパフォーマンスを大幅に向上させます。
テーブルにインデックスがなく、TABLOCK が指定されている場合、複数のクライアントが同時に 1 つのテーブルを読み込むことができます。
既定では、ロック動作はテーブル オプション table lock on bulk load によって決定されます。

       詳細については、「一括インポートのロック動作の制御」を参照してください。
   CHECK_CONSTRAINTS
       一括インポート操作中、対象テーブルまたはビューに対するすべての制約を検証します。CHECK_CONSTRAINTS ヒントを指定しない場合、CHECK 制約および FOREIGN KEY 制約は無視され、

操作の後でテーブルの制約は信頼されていないものとしてマークされます。

       メモ :
       UNIQUE、PRIMARY KEY、および NOT NULL 制約は常に適用されます。
       テーブル全体の制約は、任意の時点で必ず検証してください。

一括インポート操作の前にテーブルが空でなかった場合、制約を再検証するコストは、増分データに CHECK 制約を適用するコストを超える場合があります。
したがって、通常は、増分一括インポート時の制約チェックを有効にすることをお勧めします。

       入力データに制約違反の行が含まれている場合などは、制約を無効 (既定の動作) にできます。

CHECK 制約を無効にした場合、データをインポートした後で、Transact-SQL ステートメントを使用して無効なデータを削除できます。

       メモ :
       SQL Server 2005 では、bcp によって新しいデータ検証とデータ チェックが実行されます。

これにより、既存のスクリプトがデータ ファイル内にある無効なデータに対して実行された場合、このスクリプトは失敗する可能性があります。

       メモ :
       -mmax_errors スイッチは、制約チェックには適用されません。
       詳細については、「一括インポート操作による制約チェックの制御」を参照してください。
   FIRE_TRIGGERS
       in 引数と共に指定されている場合、一括コピーの操作時に、コピー先のテーブル上で定義されている挿入トリガを実行します。

FIRE_TRIGGERS が指定されていない場合は、挿入トリガは実行されません。
FIRE_TRIGGERS は、out 引数、queryout 引数、および format 引数では無視されます。

       詳細については、「データの一括インポート時のトリガ実行の制御」を参照してください。

　外部リンク

参考書籍

amazon.gif

サイト内リンク

SQL Server系

その他のDB系

OS系