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]
参照

1件のフィードバック to “PowerShell でデータベースのすべてのデータをエクスポートする”

  1. bcp ユーティリティによるデータのエクスポートおよびインポート « Do Design Space Says:

    […] 文字形式を使用したデータのインポートまたはエクスポート PowerShell でデータベースのすべてのデータをエクスポートする   カテゴリー: データベース. タグ: bcp, SQL Server. コメントする […]


コメントを残す