PowerShell でデータベースのすべてのデータをエクスポートする

PowerShell では .NET のライブラリを呼び出せるとのことなので、その記述方法を学ぶために、
データベースのすべてのデータを CSV ファイルにエクスポートするスクリプトをサンプルとして作成してみました。
テーブル名をあらかじめ指定しておかなければなりませんでしたが、今回はテーブル名を動的に取得します。
 
アプローチとしては、まず C# で作成し、そのコードを PowerShell スクリプトに置き換えました。
テーブル名はデータベースのメタデータから取得します。
 
C# のコードはこちらです。

string instanceName = @".\SQLEXPRESS";
string databaseName = "Tfs_DefaultCollection";
string outDirPath = "Output";

string commandText = "select [name] from sys.tables order by [name]";
string connectionString = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True",
    instanceName, databaseName);

DataTable dt = new DataTable();  

using (SqlDataAdapter da = new SqlDataAdapter(commandText, connectionString))
{
    da.Fill(dt);
}  

Directory.CreateDirectory(outDirPath);  

foreach (DataRow dr in dt.Rows)
{
    Process.Start("bcp"string.Format(@"[{2}].[dbo].[{0}] out {3}\{0}.csv -S {1} -T -w -t ,",
        (string)dr[0], instanceName, databaseName, outDirPath));
}  


 
これを PowerShell スクリプトに変換すると、次のようになります。
ファイル名を「ExportData.ps1」として保存します。

$instanceName = ".\SQLEXPRESS"
$databaseName = "Tfs_DefaultCollection"
$outDirPath = "Output"
 
$commandText = "select [name] from sys.tables order by [name]"
$connectionString = [String]::Format("Data Source={0};Initial Catalog={1};Integrated Security=True", $instanceName, $databaseName)
 
$dt = New-Object System.Data.DataTable
$da = New-Object System.Data.SqlClient.SqlDataAdapter($commandText, $connectionString)
[void]$da.Fill($dt)
$da.Dispose()
 
if (-! (Test-Path $outDirPath))
{
    New-Item $outDirPath -type directory
}
 
$dt.Rows | ForEach-Object { bcp ([String]::Format("[{0}].[dbo].[{1}]", $databaseName, $_[0])) out ([String]::Format("{0}\{1}.csv", $outDirPath, $_[0])) -S $instanceName -T -w -t "," }

 
ExportData.ps1 を右クリックして [PowerShell で実行] をクリックするとデータがエクスポートされます。
ちなみに、この例では Team Foundation Server 2010 の既定のチーム プロジェクト コレクションである 
Tfs_DefaultCollection データベースを対象としており、全部で 213 テーブルあります。
 
 
 
注意点
(1) 既定では PowerShell の実行ポリシーが Restricted に設定されているため、.ps1 ファイルを PowerShell で実行できません。
     このことは Get-ExecutionPolicy コマンドレットを実行すれば確認できます。
     実行ポリシーを緩める必要があり、そのためには次のように Set-ExecutionPolicy コマンドレットを実行します。
Set-ExecutionPolicy RemoteSigned
(2) 上記の例ではコレクション内でループさせるために ForEach-Object コマンドレットを使用していますが、
     C# の foreach に近い構文を使用することもできます (Windows PowerShell 入門(5)-制御構文)。
 
(3) 逆に、データをインポートする場合は外部参照制約に注意する必要があります。
     親テーブルから先にデータをインポートしなければなりませんが、
     次のようなクエリでテーブルの親子関係を取得すれば何とかできそうです。
select [ct].[name] as [ChildTableName], [pt].[name] as [ParentTableName]
from sys.foreign_keys as [f]
inner join sys.tables as [ct] on [f].[parent_object_id] = [ct].[object_id]
inner join sys.tables as [pt] on [f].[referenced_object_id] = [pt].[object_id]
参照

bcp ユーティリティによるデータのエクスポートおよびインポート

SQL Server 上のテーブルのデータをテキスト形式のファイルにエクスポートしたり、
逆にインポートしたりする方法について記述します。
 
SQL Server 認証を使用して、Table1, Table2 テーブルのデータを Unicode かつカンマ区切り形式のファイルとして
それぞれ Table1.csv, Table2.csv にエクスポートするには、以下のようなコマンドを実行します。

@echo off
 
set dbServer=localhost\SQLEXPRESS
set dbUser=user1
set dbPassword=P@ssw0rd
 
bcp [Database1].[dbo].[Table1] out Table1.csv -S %dbServer% -U %dbUser% -P %dbPassword% -w -t ,
bcp [Database1].[dbo].[Table2] out Table2.csv -S %dbServer% -U %dbUser% -P %dbPassword% -w -t ,
 
pause

 
逆に、Table1.csv, Table2.csv のデータをそれぞれ Table1, Table2 テーブルにインポートするには、out を in に変更するだけです。

@echo off
 
set dbServer=localhost\SQLEXPRESS
set dbUser=user1
set dbPassword=P@ssw0rd
 
bcp [Database1].[dbo].[Table1] in Table1.csv -S %dbServer% -U %dbUser% -P %dbPassword% -w -t ,
bcp [Database1].[dbo].[Table2] in Table2.csv -S %dbServer% -U %dbUser% -P %dbPassword% -w -t ,
 
pause

 
注意点
(1) Windows 認証を使用する場合は、-U および -P の代わりに -T を使用します。
bcp [Database1].[dbo].[Table1] out Table1.csv -S %dbServer% -T -w -t ,
(2) 「-w」の部分は Unicode 形式で出力することを示します。
他の文字エンコーディングで出力する場合は、「-c -C codepage」を指定します。
「-C codepage」を省略した場合は Shift_JIS となります。
例えば、UTF-8 で出力する場合は次のようにします。
bcp [Database1].[dbo].[Table1] out Table1.csv -S %dbServer% -U %dbUser% -P %dbPassword% -c -C 65001 -t ,
ただし、試してみたところ、どの文字エンコーディングでも利用可能というわけではないようです。
利用可: Shift_JIS (932)、JIS (20932)、ISO-2022-JP (50220)、UTF-8 (65001)
利用不可: Unicode (1200)、Unicode ビッグ エンディアン (1201)、EUC-JP (51932)
なお、Excel で CSV ファイルとして開く場合には、Shift_JIS でないと正常に読み込めません。
また、-w、-c の他に、-n (ネイティブ形式) および -N (Unicode ネイティブ形式) があります。
 
(3) 「-t ,」の部分はカンマ区切りであることを示します。省略した場合はタブ区切りとなります。
 
(4) NULL 値は空文字列として出力され、空文字列は \0 (16 進表記で 00 の制御文字) として出力されます。
 
(5) リレーションシップがある場合、親テーブルから先にインポートする必要があります。
 
(6) テーブル名を動的に取得する方法については、PowerShell でデータベースのすべてのデータをエクスポートするをご覧ください。
 
バージョン情報
SQL Server 2005, 2008, 2008 R2
 
 
カテゴリー: データベース. タグ: , . 1 Comment »