SQL Server のテーブルのデータを INSERT 文で吐き出す PowerShell スクリプト

PowerShell sql server cw_owashi

クロスワープの大鷲です。
ご無沙汰しております。

本記事は PowerShell Advent Calendar 2015 の 7 日目です。
昨日は素敵なおひげさんのコマンドレットを使う時に知っておくとちょっとだけ便利なことでした。

すいません、今回の記事は既出ネタです。

現在、とある社内向けアプリケーションを開発中です。
普段はローカルマシン上の IIS Express と SQL Server で開発をしているのですが、週に一度、社内の共有環境にデプロイをして、関係者にチェックしてもらっています。
そうすると、ローカル環境と共有環境で SQL Server のデータを同期する必要があります。
手段もツールもいろいろあるとは思いますが、私は特にツールを利用しなくても可能な手段として、SQL Server Management Studio（SSMS）の「スクリプトの生成」機能を利用していました。

f:id:cw_owashi:20151207111024p:plain

しかし、これが面倒なんですね。
同期したいのは一部のテーブルのデータだけなのですが、毎度毎度、対象のテーブルを選択して、スクリプトの生成オプションを指定して…という作業をしなければなりません。
SSMS の一部の機能では、GUI で行った操作をスクリプト化することができます。
しかし、この「スクリプトの生成」機能は自動化できません。
そこで、自動化のためのスクリプトを PowerShell で組むことにしました。

こういうことは、SQL Server 管理オブジェクト（SQL Server Management Object：SMO）の機能を使うことで実現できます。
クラス一覧を適当に眺めてみますと、Scripter というクラスがうってつけのようです。

そうは言っても、SMO を使ったプログラミングをしたことがありませんので、まずはチュートリアルからということで、SMO プログラムの作成というページから順に追いかけていたのですが…。
Scripter について調べていて見つけたスクリプトというページの末尾に、もっと簡単なコードが載っていることに気が付きました。
PowerShell を使うなら、sqlps モジュールを使うことで、サーバーへの接続周りなどは恐ろしく楽をすることができます。

というわけで、完成したスクリプトがこちらです。


gisteac97d8e055ce71ad6ee

sqlps は SMO 上に構築されているので、このように互換性があるのですね。
PowerShell では .NET のオブジェクトが利用できるので、コマンドレットの機能だけでは足りない部分を .NET のコードで補ったり、コードだけでは面倒なところをコマンドレットを使って楽をしたりといったことが自然にできるのは素晴らしい点だと思います。

最後に、いくつか注意点など。

  • sqlps の Get-ChildItem は -Include オプションをサポートしないようです。そのため、一旦全部取ってきてから自分でフィルタリングしています。
  • SSMS でスクリプトを生成する場合、データの削除と生成のスクリプトを一度に作ることができますが、SMO ではできません。ScriptingOptions.ScriptDrops を true にすると削除のスクリプトしか出力されませんので、まずこのプロパティを true にして一度実行し、その後で false にして再度実行する必要があります。
  • 社内環境で試したところ、接続できないサーバーがありました。sqlps を使うと、接続周りを簡単に出来るのは良いのですが、細かい制御が効きません。RPC 通信ができるかとか、Windows 認証が有効になっているかとか、接続できるための条件があるのだと思います。今回はこの辺りは詳しく調べていないのですが、場合によっては SMO のコードを書く必要があるかもしれません。
  • 今回は Scripter.EnumSriptWithList というメソッドを利用しました。Scripter には他にも、EnumScript や Script というメソッドもあります。Script メソッドでは削除のスクリプトが生成できないようなので今回は除外しました。EnumScript と EnumScriptWithList の違いは、WithList の方はオブジェクトの依存関係を考慮して、適切な順番でスクリプトを生成してくれるということのようです。
  • マシンにインストールされている SMO のバージョンと sqlps のバージョンが異なると正常に機能しません。今回、SQL Server 2014 用の SMO がインストールされているのに、SQL Server 2012 用の sqlps が読み込まれるということがありました。sqlps は、SQL Server 2012 用も 2014 用もモジュールのバージョンが 1.0 なので、ロードし分けるのが難しい仕様になっています（パスを指定して読み込めば可能だと思います）。不要なバージョンはアンインストールしてしまいましょう。

PowerShell の話題なのか SQL Server の話題なのかよくわからなくなってしまいましたが、御勘弁ください。
明日は PowerShell と言えばこの方、日本マイクロソフトの安納順一さんです。

atnd.org

「CROSSWARP」および「MODD」は株式会社クロスワープの登録商標です。