使用 PowerShell 來產生 database schema script

前一篇文章提到了 DAC ，但是遇到了 DAC 匯出失敗的問題，看訊息是部分資料表的因素，但是那些資料又是客戶自己的需求，所以利用 sqlserver 的產生 script 來進行結構備份紀錄，而自動化紀錄可以省時省力，此篇透過 PowerShell 來自動產生與處理

PowerShell ISE

要撰寫 PowerShell 可以透過 PowerShell ISE 這是一個方便撰寫的工具

撰寫內容

首先簡單說明處理流程

將會依使用者變動的部分設定為變數
檢查目的地資料夾是否存在，並建立
透過 SMO 建立連線
匯出設定
匯出結構

以下將程式與註解說明

# 最後儲存匯出結構的路徑資料夾
$Filepath = 'D:\MyScriptsDirectory'

# 資料庫連線的 data source
$DataSource = '127.0.0.1'

# 資料庫名稱
$Database = 'databaseName'

# 取得今天日期，後續會利用此日期當檔案名稱的一部份
$today = Get-Date -Format yyyy_MM_dd

# 防呆設計，當目的資料夾不存在時，建立資料夾
$homedir = "$Filepath\"
if (!(Test-Path -path $homedir)) {
  Try { New-Item $homedir -type directory | out-null }
  Catch [system.exception] {
    Write-Error "error while creating '$homedir' $_"
    return
  }  
}

# 開啟或關閉指令碼偵錯功能、設定追蹤層級和切換嚴格模式
set-psdebug -strict

# 可自訂 Windows PowerShell 行為的變數
$ErrorActionPreference = "stop"

# 讀取 SMO assembly, 假如使用 SQL 2008 DLLs 需要載入 SMOExtended 和 SQLWMIManagement libraries
$ms = 'Microsoft.SqlServer'
$v = [System.Reflection.Assembly]::LoadWithPartialName( "$ms.SMO")
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
  [System.Reflection.Assembly]::LoadWithPartialName("$ms.SMOExtended") | out-null
}

$My = "$ms.Management.Smo" #

# 設定連線資訊
$mySrvConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$mySrvConn.ServerInstance = "$DataSource"
$mySrvConn.MultipleActiveResultSets = $true
$mySrvConn.LoginSecure = $false
$mySrvConn.Login = "sa"
$mySrvConn.Password = "1qaz@WSX"
$mySrvConn.DatabaseName = "$Database"

$s = new-object ("$My.Server") $mySrvConn

# 進行連線
if ($s.Version -eq $null ) {Throw "Can't find the instance $Datasource"}
$db = $s.Databases[$Database]
if ($db.name -ne $Database) {Throw "Can't find the database '$Database' in $Datasource"};

$transfer = new-object ("$My.Transfer") $db

# 匯出設定
$CreationScriptOptions = new-object ("$My.ScriptingOptions")
# yes, we want these
$CreationScriptOptions.ExtendedProperties = $true
# and all the constraints
$CreationScriptOptions.DRIAll = $true
# Yup, these would be nice
$CreationScriptOptions.Indexes = $true
# This should be included when scripting a database
$CreationScriptOptions.Triggers = $true
# this only goes to the file
$CreationScriptOptions.ScriptBatchTerminator = $true
# of course
$CreationScriptOptions.IncludeHeaders = $true;
#no need of string output as well
$CreationScriptOptions.ToFileOnly = $true
# not necessary but it means the script can be more versatile
$CreationScriptOptions.IncludeIfNotExists = $true
$CreationScriptOptions.Filename = "$($FilePath)\$($Database)_$($today)_Build.sql";
$transfer = new-object ("$My.Transfer") $s.Databases[$Database]

# tell the transfer object of our preferences
$transfer.options = $CreationScriptOptions
$transfer.ScriptTransfer()
"All done"

這是按照 Automated Script-generation with Powershell and SMO 此篇文章的資料，重新調整撰寫的範本，目的是設定排程進行定期結構備份，後續還可以參考 Database Deployment: The Bits – Database Version Drift 進行備份比較等…