#眉標=SQL Server 2008、PowerShell、XML #副標=實用SQL Server 2008（1） #大標=整合PowerShell應用範例 #作者=文／圖 胡百敬 ================== 程式1 #LoadProvider.ps1 #載入SQL Server provider extensions #使用方式：Powershell -NoExit -Command "& '.\ LoadProvider.ps1'" #設定發生錯誤後，要如何進行，可以設定：ontinue(預設)、silentlycontinue以及stop $ErrorActionPreference = "Stop" #若機器上沒有安裝SQL Server Powershell Snap-in就直接跳離載入的動作 $sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps" if (Get-Item $sqlpsreg -ErrorAction "SilentlyContinue") { $item = Get-ItemProperty $sqlpsreg $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path) } else { throw "未安裝SQL Server Powershell "} #丟出例外，停止執行 #要預先載入的組件列表，注意，在provider未用到之前，大部分的組件已經被載入 #如果只需要SQL Server Provider，則不需要以下的步驟 #以節省shell所使用的資源 $assemblylist = "Microsoft.SqlServer.Smo", "Microsoft.SqlServer.Dmf ", "Microsoft.SqlServer.SqlWmiManagement ", "Microsoft.SqlServer.ConnectionInfo ", "Microsoft.SqlServer.SmoExtended ", "Microsoft.SqlServer.Management.RegisteredServers ", "Microsoft.SqlServer.Management.Sdk.Sfc ", "Microsoft.SqlServer.SqlEnum ", "Microsoft.SqlServer.RegSvrEnum ", "Microsoft.SqlServer.WmiEnum ", "Microsoft.SqlServer.ServiceBrokerEnum ", "Microsoft.SqlServer.ConnectionInfoExtended ", "Microsoft.SqlServer.Management.Collector ", "Microsoft.SqlServer.Management.CollectorEnum" #載入組件 foreach ($asm in $assemblylist) { $asm = [Reflection.Assembly]::LoadWithPartialName($asm)} #設定SQL 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 #載入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 #清空畫面 #cls Write-Host -ForegroundColor Yellow 'SQL Server Powershell extensions 已被載入' Write-Host Write-Host -ForegroundColor Yellow '鍵入 "cd SQLSERVER:\" 已進到 provider' Write-Host Write-Host -ForegroundColor Yellow '若需要更多資訊，鍵入 "help SQLServer"' #顯示所有可用的Windows PowerShell提供者清單 get-psprovider # ========================================== =======<反灰>============ help about_commonparameters ========================================== =======<反灰>============ http://blogs.msdn.com/powershell/archive/2006/11/03/erroraction-and-errorvariable.aspx ========================================== ================== 程式2 #設定-通用的變數 Set-Variable -scope Global -name machineName -Value "$env:ComputerName"; #透過 $env: 命名空間，取得系統環境變數 ComputerName 的值，也就是本機電腦名稱 #預設的執行個體名稱可用Default Set-Variable -scope Global -name instanceName -Value "Default"; Set-Variable -scope Global -name scriptPath -Value "C:\PowerShellScript\" #先前定義要載入的SQL2K8 Provider .\LoadProvider.ps1 ========================================== ================== 程式3 .\01SetupEnv.ps1 #Text Driver #databases.txt檔案內容如下： #AdventureWorks #Northwind Push-Location #呈現檔案內每一行的內容 get-Content ($scriptPath + "databases.txt") | foreach {write-Host "操作資料庫 " $_ } #例如，使用SQL 2008提供的Provider，表列資料庫內的資料表 get-Content ($scriptPath + "databases.txt") | foreach ` {CD sqlserver:\sql\$machineName\$instanceName\databases\$_\tables; ` write-Host "資料庫內的資料表： " $_ ; DIR | MORE;} Pop-Location #透過XML Driver讀取XML文檔內容 $doc = [xml]( get-Content ($scriptPath + "Objects.xml") ) #透過XML DOM取得節點內容： ($doc).SelectNodes('//servers/server[@servername="SQL2K8"]/databases') #透過XML DOM取得節點內容： ($doc).SelectNodes('//servers/server[@servername="SQL2K8"]/databases/database[@databasename="AdventureWorks"]/table') #遞迴表列節點... foreach ($database in $doc.SelectNodes("//servers/server/databases/database")) {$database.databasename} ========================================== =======<反灰>============ (local)
========================================== =======<反灰>============ PS C:\PowerShellScript> 'Hello ' + ` >> 'World' >> ========================================== =======<反灰>============ PS C:\PowerShellScript> "Hello >> ` >> World" >> ========================================== =======<反灰>============ PS C:\> [int[]][char[]]"Hello" ========================================== ================== 程式4 #函數搭配參數 Function objectPath {"路徑為" + $args[0] + "`\" + $args[1] } #呼叫函數，因為如同一般命令，所以參數間不以逗號分隔 objectPath a b Function objectPath2 ($x, $y) { $ObjectPath = $x + "`\" + $y write-Host "路徑為 $ObjectPath" } objectPath a b #函數使用"param" #param的宣告必須在第一行，在它之前只能有空白或註解 Function objectPath3 { param ($x, $y) $ObjectPath = $x + "`\" + $y write-Host "路徑為 $ObjectPath" } objectPath3 a b ========================================== =======<反灰>============ Function objectPath4($x){$x[0] + "\" + $x[1]} ========================================== =======<反灰>============ objectpath4 a,b ========================================== =======<反灰>============ http://technet.microsoft.com/zh-tw/magazine/cc895642.aspx ========================================== =======<反灰>============ PS C:\PowerShellScript> . .\02SimpleControl.ps1 ========================================== ================== 程式5 #回報紀錄機制 #事件紀錄(Event Log) Function log_This { $log = New-Object System.Diagnostics.EventLog('Application') $log.set_source($log_ApplicationName) $log.WriteEntry($log_Message) } #範例-呼叫函數傳遞內容時，不用參數只用變數 $log_ApplicationName="自訂的應用程式名稱" $log_Message="輸入需要記錄的資訊" #呼叫函數，你可以用系統的事件檢視器檢視結果 log_This #讀取事件紀錄： get-Eventlog application | where-Object {$_.Message -eq "輸入需要記錄的資訊"} get-Eventlog application | where-Object {$_.source -eq "自訂的應用程式名稱"} #清除所有的事件紀錄-要小心使用! #get-EventLog -list | % {$_.Clear()} ========================================== =======<反灰>============ Function WriteFile($Msg) { $sw = New-Object System.IO.StreamWriter "C:\PowerShellScript\myLog.log",True $sw.WriteLine($Msg) $sw.Close() } WriteFile("Hello Msg") ========================================== ================== 程式6 #錯誤處理 Function handle_This ($appName) { # 建立錯誤訊息字串 $log_Message = "Error Category: " + $error[0].CategoryInfo.Category $log_Message = $log_Message + ". 執行的物件：" + $error[0].TargetObject $log_Message = $log_Message + " 錯誤訊息：" + $error[0].Exception.Message $log_Message = $log_Message + " 錯誤訊息： " + $error[0].FullyQualifiedErrorId write-Host "錯誤發生於" $appName "`! 檢查事件紀錄，以取得更多資訊" #自動傳送訊息給先前撰寫的Logging函數： $log_ApplicationName = $appName log_This } #錯誤處理範例，執行某項工作 Function ErrorHappensHere { SomeFun #沒有這個指令或函數，所以發生錯誤 Trap { #進入錯誤處理 handle_This "SomeFun" #若發生錯誤後，就停下來，可以使用break #break; #或是繼續進行 continue; } } #呼叫上述會發生錯誤的函數 ErrorHappensHere write-Host "檢查事件紀錄" # Check the error get-Eventlog application | where-Object {$_.Message -like "Error*"} write-Host "完成錯誤輸出" #透過電子郵件寄發訊息 Function mail_This ($mail_From, $mail_To, $mail_Subject, $mail_Body, $mail_Attachment) { #簡易的電子郵件設定 # $smtp = new-object Net.Mail.SmtpClient("localhost") # $smtp.Send($mail_From, $mail_To, $mail_Subject, $mail_Body) $smtpServer = "localhost" $msg = new-object Net.Mail.MailMessage $att = new-object Net.Mail.Attachment([string]$mail_Attachment) $smtp = new-object Net.Mail.SmtpClient $smtpServer $msg.From =$mail_From $msg.To.Add($mail_To) $msg.Subject = $mail_Subject $msg.Body = $mail_Body $msg.Attachments.Add($att) $smtp.Send($msg) } mail_This "PowerShell@localhost" "Someone@localhost" "來自 PowerShell 的通知" "測試從 PowerShell 發送電子郵件" "C:\PowerShellScript\myLog.log" # System Tray "Balloons" Function balloon_This ($balloon_Title, $balloon_Text) { [void] [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") $objNotifyIcon = New-Object System.Windows.Forms.NotifyIcon $objNotifyIcon.Icon = "C:\PowerShellScript\myLogIcon.ICO" $objNotifyIcon.BalloonTipIcon = "Info" # Info, Warning, Error $objNotifyIcon.BalloonTipTitle = $balloon_Title $objNotifyIcon.BalloonTipText = $balloon_Text $objNotifyIcon.Visible = $True $objNotifyIcon.ShowBalloonTip(10000) } balloon_This "注意！！" "要開始做些事情。事情進行時，可以透過此傳送些訊息，酷吧！" #轉成HTML檔案輸出 Function web_This ($web_Title) { #以一些CSS styles當作HTML Header $formatHTML = "" #將系統當下執行的Process狀態以HTML格式輸出 Get-Process | ConvertTo-Html -head $formatHTML –title $web_Title | Out-File ("c:\PowerShellScript\" + $web_Title + ".htm") } web_This "ProcessDetail" ========================================== ================== 程式7 #你可以結合前一段範例，將下述的結果轉成網頁，或是透過電子郵件寄送... write-Host " ------------------------------------------------ 使用SQL 2008的Provider，執行T-SQL查詢語法" CD sqlserver:\sql\$machineName\$instanceName\databases\master\ invoke-SQLCMD -Query "SELECT @@VERSION" write-Host " ------------------------------------------------ 呈現使用者定義大過某個紀錄數量的資料表列表" CD sqlserver:\sql\$machineName\$instanceName\databases\AdventureWorks\tables DIR | where-Object {$_.RowCount -gt 10000} | sort-Object -property RowCount | select-Object Name, RowCount write-Host " ------------------------------------------------ 檢查備份的時間" CD SQLSERVER:\SQL\$machineName\$instanceName\Databases DIR | WHERE {((get-date)-($_.LastBackupDate)).days -gt 1} | sort-Object -property LastBackupDate | select-Object LastBackupDate, Name write-Host " ------------------------------------------------ 備份資料庫" CD sqlserver:\sql\$machineName\$instanceName\databases\ DIR | %{[string]$_.Name + ' 最後備份日期：' + $_.LastBackupDate} DIR | where{ (((get-date)-($_.LastBackupdate)).days -gt 1) -and $_.name -eq "Northwind"} | ` %{$dbname= $_.Name;write-host "$dbname"; $_.Refresh(); invoke-sqlcmd -Server "$machineName\$instanceName" -query "BACKUP DATABASE [$dbname] TO DISK = N'C:\PowerShellScript\temp\$dbname.bak' WITH INIT";} write-Host " ------------------------------------------------ 檢查 SQL Error Logs，轉成 HTML" # 可以參照 03Feedback.ps1 內容： # web_This "SQLErrors" (get-item SQLSERVER:\SQL\$machineName\$instanceName).ReadErrorLog() | ` where {$_.Text -like "Start*"} | ConvertTo-Html -property LogDate, ProcessInfo, Text ` -body "執行個體： $machineName\$instanceName" -title "SQL Server Error Logs" | ` Out-File C:\PowerShellScript\temp\SQLErrors.htm -Append write-Host " ------------------------------------------------ 比較資料庫物件的 Scripts" CD SQLSERVER:\SQL\$machineName\$instanceName\Databases\AdventureWorks\Tables # 將資料表的定義寫入檔案中 (get-item HumanResources.Employee).Script() | out-File C:\PowerShellScript\temp\before.sql # Time passes, table changes # 可能會需要更新一下物件定義 # (get-item HumanResources.Employee).Refresh() invoke-SQLCMD -InputFile C:\PowerShellScript\temp\DropCreateHumanResourcesEmployee.sql # 建立資料表的 T-SQL Script 到檔案中 (get-item HumanResources.Employee2).Script() | out-File C:\PowerShellScript\temp\after.sql # 比較兩個檔案內容的異同 compare-object (get-content C:\PowerShellScript\temp\before.sql) (get-content C:\PowerShellScript\temp\after.sql) | out-File C:\PowerShellScript\temp\CompareResult.txt ========================================== ================== 程式8 write-Host " ------------------------------------------------ 透過 .NET SQL Native Client 連接到 SQL Server 並取得記錄" $sqlConnection = new-object System.Data.SqlClient.SqlConnection $sqlConnection.ConnectionString = "Data Source=$machineName;Initial Catalog=AdventureWorks;Integrated Security=True" $sqlConnection.Open() $sqlCommand = new-object System.Data.SqlClient.SqlCommand $sqlCommand.CommandText="select top 5 ContactID, FirstName, LastName, EmailAddress, Phone from Person.Contact" $sqlCommand.Connection=$sqlConnection $sqlreader = $sqlCommand.ExecuteReader() while ($sqlReader.Read()) { $sqlReader["FirstName"] + " " + $sqlReader["LastName"]} $sqlreader.Close() write-Host " ------------------------------------------------ 繼續將資料放入ADO.NET的DataSet" $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $sqlCommand $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet) $SqlConnection.Close() $DataSet.Tables[0] #將資料直接轉成字串輸出 $DataSet.Tables[0] | Foreach-Object { [string]$_.ContactID + ": " + $_.FirstName + ", " + $_.LastName + ", " + $_.EmailAddress } write-Host " ------------------------------------------------ 利用SMO執行命令-以紀錄筆數排名，取前十名的資料表" #[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") $sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") "$machineName" #表列所有的資料庫 foreach($sqlDatabase in $sqlServer.databases) {$sqlDatabase.name} #檢視可以操作的物件屬性方法 $sqlServer | get-member | ConvertTo-Html -property Name, MemberType, Definition -body "Microsoft.SqlServer.Management.Smo.Server 類別" -title "Smo.Server 類別的屬性方法" | Out-File C:\PowerShellScript\temp\SmoServer.htm #將前十名的資料表名稱輸出到檔案 $sqlServer.Databases["AdventureWorks"].Tables | sort -property rowcount -desc | Select-Object -First 10 | Format-Table schema, name, rowcount -AutoSize | out-File C:\PowerShellScript\temp\TopTenTables.txt write-Host " ------------------------------------------------ 利用WMI-設定或取得Windows服務" get-content C:\PowerShellScript\servers.txt | foreach { $class = Get-WmiObject -computername $_ -namespace root\Microsoft\SqlServer\ComputerManagement10 -class SqlService foreach ($classname in $class) {write-host "SQL Server 相關服務：" $_ " : "$classname.DisplayName} } #可以做的範例語法： #停掉服務 # stop-service -displayName $classname.DisplayName #設定服務帳號 #$classname.SetServiceAccount(“Account Name", “New Password") #啟動服務 #start-service -displayName $classname.DisplayName ========================================== 程式8為經由ADO.NET、SMO、WMI等物件，存取SQL Server資料或操控服務。最後做一個綜合性的示範，透過SMO物件備份資料庫，雖然在前述的程式碼列表7中，也有透過T-SQL語法完成備份，但此處的示範綜合了多種PowerShell技巧。(程式9) ================== 程式9 #05backup.ps1 #對指定的伺服器執行個體內，所有使用者資料庫先做完整備份， #再進行交易紀錄備份 [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoExtended') | out-null Function Backup($ServerInstance) { $s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "$ServerInstance" $bkdir = $s.Settings.BackupDirectory $dbs = $s.Databases $dbs | foreach-object { 　$db = $_ 　if ($db.IsSystemObject -eq $False -and $db.IsMirroringEnabled -eq $False) { $dbname = $db.Name Write-Host "備份資料庫 $dbname" $dt = get-date -format yyyyMMddHHmmss $dbbk = new-object ("Microsoft.SqlServer.Management.Smo.Backup") $dbbk.Action = "Database" $dbbk.BackupSetDescription = $dbname + " 的完整備份" $dbbk.BackupSetName = $dbname + " Backup" $dbbk.Database = $dbname $dbbk.MediaDescription = "Disk" $FileName=$bkdir + "\" + $dbname + "_db_" + $dt + ".bak" $dbbk.Devices.AddDevice($FileName, "File") $dbbk.SqlBackup($s) # 若 Recovery Model 是 Simple ，則 Value 屬性值是 3 if ($db.recoverymodel.value__ -ne 3) { $dt = get-date -format yyyyMMddHHmmss $dbtrn = new-object ("Microsoft.SqlServer.Management.Smo.Backup") $dbtrn.Action = "Log" $dbtrn.BackupSetDescription = $dbname + " 的交易紀錄備份" $dbtrn.BackupSetName = $dbname + " Backup" $dbtrn.Database = $dbname $dbtrn.MediaDescription = "Disk" $dbtrn.Devices.AddDevice($bkdir + "\" + $dbname + "_tlog_" + $dt + ".trn", "File") $dbtrn.SqlBackup($s) } } 　} } #以互動的方式詢問需要備份的SQL Server執行個體 [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') | out-null $Instance=[Microsoft.VisualBasic.Interaction]::InputBox("請輸入需要備份的 SQL Server 執行個體","執行個體名稱","localhost") Backup $Instance ========================================== =======<反灰>============ http://blogs.msdn.com/buckwoody/archive/2008/11/21/pass-2008-conference-day-five.aspx ==========================================