SQL のオブジェクトを PowerShell を用いてスクリプト化してみる

技術系…なのか？

SQL 2000までは SQL-DMO を使って VBScript からゴリっとスクリプト化して保存してましたが、SQL 2008 は SQL-DMO をサポートしなくなったので後継版の SMO を使って PowerShellから投げてみました。
もちろん SQLジョブで PowerShell がネイティブにサポートしているのでやりやすいのではないかと。
で、書いたのがコレ。

【SQLスクリプトの生成】
　$svname = "サーバー名（インスタンス名）"
　$scon = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
　$scon.ServerInstance = $svname
　$scon.LoginSecure = $true
　$scon.Connect

　$server = New-Object Microsoft.SqlServer.Management.Smo.Server $scon

　$scripter = New-Object Microsoft.SqlServer.Management.Smo.Scripter $server
　$scripter.Options.ScriptDrops = $false　　#オブジェクトの削除スクリプトを含む場合は $true
　$scripter.Options.IncludeHeaders = $true
　$scripter.Options.Indexes = $true
　$scripter.Options.NoCollation = $true
　$scripter.Options.Permissions = $true
　$scripter.Options.Triggers = $true
　$scripter.Options.DriAll = $true
　$scripter.Options.EnforceScriptingOptions = $true
　$scripter.Options.PrimaryObject = $true
　$scripter.Options.SchemaQualify = $true
　$scripter.Options.AppendToFile = $true
　$scripter.Options.Encoding = [System.Text.Encoding]::Default
　$scripter.Options.ToFileOnly = $true

　foreach($db in $server.Databases){
　if(!$db.IsSystemObject -and $db.Name -ne "ReportServer" -and $db.Name -ne "ReportServerTempDB" ){

　$path = "保存先パス\" + $db.Name + "\"
　$delfile = $path + "*.sql"
　Remove-Item $delfile　　#今あるスクリプトの後ろに追加で書き込まれるので事前に削除します。

　#ユーザー・テーブルのスクリプト化
　$scripter.Options.File　Name = $path + "Tables.sql"
　$db.Tables | ForEach-Object{if(!$_.IsSystemObject ) {$scripter.Script($_)}}

　#ユーザー・ビューのスクリプト化
　$scripter.Options.FileName = $path + "Views.sql"
　$db.Views | ForEach-Object{if(!$_.IsSystemObject) {$scripter.Script($_)}}

　#ユーザー・ストアドプロシージャのスクリプト化
　$scripter.Options.FileName = $path + "StoredProcedures.sql"
　$db.StoredProcedures | ForEach-Object{if(!$_.IsSystemObject) {$scripter.Script($_)}}

　#ユーザー定義関数のスクリプト化
　$scripter.Options.FileName = $path + "UserDefinedFunctions.sql"
　$db.UserDefinedFunctions | ForEach-Object{if(!$_.IsSystemObject) {$scripter.Script($_)}}

　#トリガーのスクリプト化
　$scripter.Options.FileName = $path + "Triggers.sql"
　$db.Triggers | ForEach-Object{if(!$_.IsSystemObject) {$scripter.Script($_)}}

　#ロールのスクリプト化
　$scripter.Options.FileName = $path + "Roles.sql"
　$db.Roles | ForEach-Object{if(!$_.IsSystemObject) {$scripter.Script($_)}}

　#スキーマのスクリプト化
　$scripter.Options.FileName = $path + "Schemas.sql"
　$db.Schemas | ForEach-Object{if(!$_.IsSystemObject) {$scripter.Script($_)}}

　#ユーザーのスクリプト化
　$scripter.Options.FileName = $path + "Users.sql"
　$db.Users | ForEach-Object{if(!$_.IsSystemObject) {$scripter.Script($_)}}

}}

いずれもシステム・オブジェクトを除いて作成してます。
もう少しスマートな書き方があるのかも知れませんが、私にはいっぱいいっぱいです。(^^；