PowerShell では .NET のライブラリを呼び出せるとのことなので、その記述方法を学ぶために、
データベースのすべてのデータを CSV ファイルにエクスポートするスクリプトをサンプルとして作成してみました。
以前の bcp ユーティリティによるデータのエクスポートおよびインポートで紹介した方法では
テーブル名をあらかじめ指定しておかなければなりませんでしたが、今回はテーブル名を動的に取得します。
アプローチとしては、まず 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]
参照
2010年10月1日 09:14
[…] 文字形式を使用したデータのインポートまたはエクスポート PowerShell でデータベースのすべてのデータをエクスポートする カテゴリー: データベース. タグ: bcp, SQL Server. コメントする […]