普通のサーバー管理者はSQLでやるのでしょうが、日付をファイル名にする場合はやっぱりPowerShellの方が楽。
| 001 002 003 | $now = (get-date).ToString("yyyyMMddHHmmss") Backup-SqlDatabase -Database Northwind ` -BackupFile "D:\DBBackup\test\$now.bak" -ServerInstance localhost |
「ServerInstance」はローカルなら省略できても良さそうだが、省略するとエラーになった。
データベースアクセスはこれまで何度もやっているが、以下で関数が提供されていたので転載しておく。
Windows PowerShell: Windows PowerShell からデータベースにアクセスする
Powershellの第１人者が提供する関数なので使って損はないだろうと思い、プロファイルに入れておくことにした。
抽出用と更新用の関数２つ。
接続文字列のサンプルだけをちょっと追加している。
Get-DatabaseDataU –? で簡易的に接続文字列サンプルを表示させている。
また、この記事で紹介されている接続文字列のサンプルサイトはリンクしておくと役に立ちそうだ。
| 001 002 003 004 005 006 007 008 009 010 011 012 013 014 015 016 017 018 019 020 021 022 023 024 025 026 027 028 029 030 031 032 033 034 035 036 037 038 039 040 041 042 043 044 045 046 047 048 049 050 051 052 053 054 055 056 057 058 059 060 061 062 063 | $comment=@' 接続文字列サンプル (1) "Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;" (2) "Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;" '@ function Get-DatabaseDataU { param ( [string]$connectionString, [string]$query, [switch]$isSQLServer, [switch]${??} ) if(${??}) {$comment;return} if ($isSQLServer) { Write-Verbose 'in SQL Server mode' $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection } else { Write-Verbose 'in OleDB mode' $connection = New-Object -TypeName System.Data.OleDb.OleDbConnection } $connection.ConnectionString = $connectionString $command = $connection.CreateCommand() $command.CommandText = $query if ($isSQLServer) { $adapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter $command } else { $adapter = New-Object -TypeName System.Data.OleDb.OleDbDataAdapter $command } $dataset = New-Object -TypeName System.Data.DataSet $adapter.Fill($dataset) $dataset.Tables[0] } function Invoke-DatabaseQueryU { param ( [string]$connectionString, [string]$query, [switch]$isSQLServer, [switch]${??} ) if(${??}) {$comment;return} if ($isSQLServer) { Write-Verbose 'in SQL Server mode' $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection } else { Write-Verbose 'in OleDB mode' $connection = New-Object -TypeName System.Data.OleDb.OleDbConnection } $connection.ConnectionString = $connectionString $command = $connection.CreateCommand() $command.CommandText = $query $connection.Open() $command.ExecuteNonQuery() $connection.close() } |
SharePointを入れると様々なDBがインストールされる。
復旧モデルが「完全」になってたりするとログの容量が増え続けて困ってしまう。
評価・テスト用だったりすると「完全」である必要はないので「単純」に変更する。
評価用にスクラップ＆ビルドしてたりすると何度もやるはめになるのでスクリプトにしてみた。
| 001 002 003 004 005 006 007 008 009 010 011 012 013 014 | $Instance = ".\sqlexpress" [void][Reflection.Assembly]::LoadWithPartialName( "Microsoft.SqlServer.Smo") #Full,BulkLogged,Simple $cModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Simple $server = New-Object Microsoft.SqlServer.Management.Smo.Server($Instance) $server.ConnectionContext.LoginSecure = $true $server.Databases | ?{-not $_.IsSystemObject} | ?{$_.RecoveryMode -ne $cModel } | %{$_.RecoveryModel = $cModel ; $_.Alter()} "Done" |
プロパティを変更した後にAlterメソッドを呼んであげないと変更が反映されないようだ。
複数のDBの中にあるテーブル定義を纏めてCSVに出力したいという要望があり、探せば何処かにありそうな気もしたが、SQLServerを扱う勉強に調度良さそうなので作ってみた。
とりあえず以下のような選択リストを表示してDBを選択させることにした。
ソースは以下のとおり。
| 001 002 003 004 005 006 007 008 009 010 011 012 013 014 015 016 017 018 019 020 021 022 023 024 025 026 027 028 029 030 031 032 033 034 035 036 037 038 039 040 041 042 043 044 045 046 047 048 049 050 051 052 053 054 055 056 057 058 059 060 061 062 063 064 065 066 067 068 069 070 | param( $OutPath=[environment]::GetFolderPath("desktop"), $Instance=".\sqlexpress" ) function Main { $outDBPath = "" $TrueSign = " " * 5 + "○" #DB一覧を表示 . ChoiceDB #選択されたDBを抽出 @($objDBs)[($DBNumbers | %{(--$_)})] | %{ . CreateDBFolder $_.name #DB名でフォルダーを作る $_.Tables | %{ $tableFullName = #Table名をそのままファイル名に Join-Path $outDBPath ($_.name + ".csv") $_.Columns | select name,datatype, @{ #Lengthプロパティ name="Length" expression={ if($_.datatype.name -match 'char'){ $_.datatype.MaximumLength } } }, @{ #Nullableプロパティ name="Nullable" expression={if($_.Nullable){$TrueSign}} }, @{ #PrimaryKeyプロパティ name="PrimaryKey" expression={if($_.InPrimaryKey){$TrueSign}} } | . OutCSV } } } function ChoiceDB() { [void][Reflection.Assembly]::LoadWithPartialName( "Microsoft.SqlServer.Smo") $server = New-Object Microsoft.SqlServer.Management.Smo.Server($Instance) $server.ConnectionContext.LoginSecure = $true $i=0 Write-Host "以下の中から対象のDBを選択して下さい。" -NoNewline Write-Host "（複数指定時はカンマ区切り）" -ForegroundColor Yellow $server.Databases | ?{-not $_.IsSystemObject} | tee -Variable objDBs | fw {(++$script:i).ToString() + ":" + $_.name} [int[]]$DBNumbers = (Read-Host "DB Number " ) -split "," if(-not $DBNumbers){exit} } function CreateDBFolder($dbname) { $outDBPath = Join-Path $OutPath $dbname if(-not (Test-Path $outDBPath)) {mkdir $outDBPath | Out-Null} } function OutCSV() { $input | Export-Csv -Path $tableFullName -NoType -Encoding "Default" } . Main Write-host "Complete" -ForegroundColor |
選択されたDB名でフォルダーを作り、そこにテーブルと同じ名前でCSVファイルを出力している。
SQLServerへのアクセスにはSMOを使った。
認証はとりあえずWindows認証を指定している。
出力したCSVをEXCELで開くとこんな感じ。
EXCELに直接出したいというニーズもありそうだが、今回はサーバーで実行するのを想定しているため、EXCELがインストールされていなくても大丈夫なようにCSVで良しとした。
一応、CSV出力の箇所はOutCSVというFunctionにしたので、ここをOutExcelとかにして置き換えればすぐに変更もできるのではないだろうか。
エラー処理を組み込んでいないので指定を間違えないようにする(^^;
色々と方法はあるのでしょうが、とりあえず「SQLServer Posershell」を使ってやってみた。
以前の説明で「SQL Server」用のPowershell環境ができていれば特に難しいことは何も無い。
| 001 002 003 004 005 | dir SQLSERVER:\sql\localhost\sqlexpress\Databases\Northwind\tables | ?{$_.name -like "order*"} | %{$name=$_.name invoke-sqlcmd -database Northwind -query "select * from $name" | Export-Csv "d:\desktop\$name.csv" -NoTypeInformation} |
テーブルに日本語が含まれる場合はEncoding指定も必要になる。
自PCでは特に問題は無かったのだが、幾つかのサーバーで試した所、うまくいかないケースがあった。
| Invoke-Sqlcmd : サーバーへの接続を確立しているときにエラーが発生しました。SQL S |
環境を比較しても有効なプロトコルとかに違いはなく、今ひとつ原因がはっきりしない。
あくまでも想像だが、認証にSQLServer認証を使用しているのが原因かもしれない。（invoke-sqlcmdでユーザーID/パスワードは指定を指定したりしても変わらない）
この場合でも、一旦カレントディレクトリをtablesのところまで変更してから実行したりすると上手く行った。
特に運用で使うわけでもなく回避策もあるので良しとする。
外部からの接続でもそうだったが、どうも認証関係がスムーズではない感じだ。
SQL Server PowerShellの環境を調査してきたが、リモート接続に難があったりしてあまり有用とは思えない。
たぶん、サーバー自身にタスクでスクリプトを仕掛けたりって使い方がメインになるのかな？
おそらくSQL Server PowerShellもSMOのラッパーだったりするのだろうから、今のところSMOを直接弄ったほうが使い勝手がよさげ。（環境的にもSMOをロードしておくだけだし）
というわけでSMOを使ったサンプルを。
| 001 002 003 004 005 006 007 008 009 | [void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") $server = New-Object Microsoft.SqlServer.Management.Smo.Server("Servername\instancename") $server.ConnectionContext.LoginSecure = $false #Windows認証の時はTrue $server.ConnectionContext.Login = "username" $server.ConnectionContext.Password = "password" $db = $server.databases["pubs"] $result = $db.ExecuteWithResults("select * from jobs") $result.tables | select -ExpandProperty rows |
SQLを投げるだけであればPowerShell: ◆PowershellでDBアクセス3あたりと大差ないと思うのだが、Serverオブジェクトを使えば管理タスクは何でもできそう。
ちなみに、PowerShell: ◆SQL Server PowerShellの環境を作るで作ったDBの容量一覧のスクリプトも、このServerオブジェクトを使って同様に取得できる。（結局中身は同じだろうし）
| 001 002 003 004 005 006 007 008 009 010 011 012 013 014 015 016 017 018 019 020 021 022 023 024 025 026 027 028 | [void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") $server = New-Object Microsoft.SqlServer.Management.Smo.Server("Servername\instancename") $server.ConnectionContext.LoginSecure = $false $server.ConnectionContext.Login = "username" $server.ConnectionContext.Password = "password" $dbinfos = @() $fmt = "#,##0.0" #データベースの数だけ繰り返し foreach ( $db in $server.Databases ) { $dbinfo = New-Object PsObject | select データベース名,DB,空き, データ,インデクス #データベース名 $dbinfo.データベース名 = $db.Name #DBの容量 $dbinfo.DB = $db.Size.ToString($fmt) + "MB" #DBの空き容量 $dbinfo.空き = ($db.SpaceAvailable / 1024).ToString($fmt) + "MB" #データの容量 $dbinfo.データ = ($db.DataSpaceUsage / 1024).ToString($fmt) + "MB" #インデックスの容量 $dbinfo.インデクス = ($db.IndexSpaceUsage / 1024).ToString($fmt) + "MB" $dbinfos += $dbinfo } $dbinfos | ft –Auto |
なお、SMOのロードにAdd-Typeを使うとエラーになる（環境にもよりそうだが）。
Add-Typeはストロングネームを指定する必要があるとの事。
| try { Add-Type -Assembly Microsoft.SqlServer.Smo } |
詳しくは以下を参照。
CTP3/V2b - Add-Type -a "Microsoft.SqlServer.Smo" won't load SMO assemblies | Microsoft Connect
sqlps環境はPowershellのスパーセットなのかと思っていたら、どうも違うようだ。
色々と使えないコマンドがあったりして使い勝手が違う。
スーパーセットではなくサブセットなのね・・・。
そこで、通常のPowershell環境で以下のスクリプトを実行するとPowershell+SQLServer用Snapin環境となるようだ。
（MS資料からの転載）SQL Server PowerShell の実行
| 001 002 003 004 005 006 007 008 009 010 011 012 013 014 015 016 017 018 019 020 021 022 023 024 025 026 027 028 029 030 031 032 033 034 035 036 037 | # # Add the SQL Server Provider. # $ErrorActionPreference = "Stop" $sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps" if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue") { throw "SQL Server Provider for Windows PowerShell is not installed." } else { $item = Get-ItemProperty $sqlpsreg $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path) } # # Set mandatory variables for the SQL Server provider # Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0 Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30 Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000 # # Load the snapins, type data, format data # Push-Location cd $sqlpsPath Add-PSSnapin SqlServerCmdletSnapin100 Add-PSSnapin SqlServerProviderSnapin100 Update-TypeData -PrependPath SQLProvider.Types.ps1xml update-FormatData -prependpath SQLProvider.Format.ps1xml Pop-Location |
こいつをドットソースで取り込むなり、Profileに入れておくなりしておけば以下のような感じで簡単にSQLを発行できるようになる。
| PS>invoke-sqlcmd -Database pubs -Query "select * from jobs" | ft -auto job_id job_desc min_lvl max_lvl |
PowerShell: ◆SQL Server PowerShellの環境を作るではローカルのSQLServerに接続した。
SQLServerの管理となれば当然リモートからなのでリモート接続を試して見た。
のだが、これがうまくいかない・・・・。
MSの資料によるとSQLServer認証の場合は仮想ドライブを作ってそこに資格情報を結び付けろとある。
| 001 002 | New-PSDrive mysql -PSProvider SqlServer ` -Root "SQLSERVER:\SQL\ServerName\Default" -Credential (Get-Credential) |
ちなみにデフォルトインスタンスでもインスタンス名は省略できず、「Default」と指定する必要があるらしい。
ここで表示された資格情報ダイアログに、 sa/hoge とか入れてやれば良いはずなのだが、以下のようなエラーが出る。
| 警告: SQL Server サービスの情報を取得できませんでした。'ServerName' の WMI |
メッセージからするとWMIレベルでエラーになっているようだ。
確かに、自PCからサーバーへはWMIで接続はできない。（まぁ、普通そうでしょ）
WMIを使ってSqlServerに認証を投げるには２種類の認証を指定しなければダメっぽ。
いろいろ調べてみたがそんな方法はなさげ・・・。
やっと以下の情報にたどり着いた。
New-PSDrive with SQL Authentication internally is using Integrated Authentication | Microsoft Connect
ん～、結局バグかい・・・。
っていうか仕様自体が破綻していない？
試しにWMIでアクセス可能なPCに対して上記スクリプトを実行したところ接続に成功した。
とはいえ、そもそもWMIでアクセス可能だったらWindows認証で良いわけなんでほとんど意味が無い。
というわけでSQLServerプロバイダーは使う環境が限られてきそうだ。
なお、テストで他PCのExpressを使う場合はリモート接続を有効にする必要がある。
SQL Server 2008 Express にリモート接続
SQL Server2008をインストールすると一緒にインストールされる。（R2の場合はインストール時の機能選択で管理ツールを選ぶとインストールされるようだ）
Expressでもwith Advanced Services 版をインストールすれば使える。
一番簡単な使い方はコマンドラインから「sqlps」と打ち込む。
するとSQL Server用の機能が組み込まれたPowershellが起動する。
SQLプロバイダーがルートになっているのでdirコマンドを打つと以下のような内容が表示される。
ルートのsqlを潜っていくとDatabasesが現れる。
この中身をまた潜っていくとTableやColumnが現れる。
というように、SQLプロバイダーのおかげでSQL Serverも通常のファイルツリーの様にアクセスが可能になっている。
あとはこれらのオブジェクトを使って様々な情報を引き出せば良い。
以下を参考にサンプルを作ってみた。
SQL Server PowerShell を使用した管理手法 第 2 回 実践編
| 001 002 003 004 005 006 007 008 009 010 011 012 013 014 015 016 017 018 019 020 021 022 023 | $server = get-item SQLSERVER:\sql\localhost\sqlexpress\databases $dbinfos = @() $fmt = "#,##0.0" #データベースの数だけ繰り返し foreach ( $db in $server.Collection ) { $dbinfo = New-Object PsObject | select データベース名,DB,空き, データ,インデクス #データベース名 $dbinfo.データベース名 = $db.Name #DBの容量 $dbinfo.DB = $db.Size.ToString($fmt) + "MB" #DBの空き容量 $dbinfo.空き = ($db.SpaceAvailable / 1024).ToString($fmt) + "MB" #データの容量 $dbinfo.データ = ($db.DataSpaceUsage / 1024).ToString($fmt) + "MB" #インデックスの容量 $dbinfo.インデクス = ($db.IndexSpaceUsage / 1024).ToString($fmt) + "MB" $dbinfos += $dbinfo } $dbinfos | ft -Auto |
結果
ちなみに、スクリプトファイルを実行するときは、
sqlps -noexit -command "d:\desktop\db.ps1"
の様に起動してあげれば良い。
