Powershell 將數據庫模式轉儲到文本文件

我希望能夠使用 Powershell 將數據庫模式轉儲到 sql 文本文件。我想要這個是因為我們的 DBA 希望能夠對送出給 SVN 的文件進行比較。如果他們看不到它包含的內容，他們將不會執行它。

我找到了許多備份到二進制 .bak 文件的腳本，但我找不到任何作為 sql 文本文件轉儲的腳本。

您如何在 Powershell 中執行此操作？

使用 dbatools 模組。

您需要從您的 svn 建構您的解決方案。使用這些 dacpacs，您可以使用以下命令。

Publish-DbaDacPackage -SqlInstance sql2017 -Database WideWorldImporters -Path C:\temp\sql2016-WideWorldImporters.dacpac -PublishXml C:\temp\sql2016-WideWorldImporters-publish.xml -GenerateDeploymentReport -ScriptOnly

參考： https ://docs.dbatools.io/#Publish-DbaDacPackage

也許這會有所幫助

https://gist.github.com/cheynewallace/9558179

# Usage:  powershell ExportSchema.ps1 "SERVERNAME" "DATABASE" "C:\<YourOutputPath>"


# Start Script
Set-ExecutionPolicy RemoteSigned

# Set-ExecutionPolicy -ExecutionPolicy:Unrestricted -Scope:LocalMachine
function GenerateDBScript([string]$serverName, [string]$dbname, [string]$scriptpath)
{
 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
 [System.Reflection.Assembly]::LoadWithPartialName("System.Data") | Out-Null
 $srv = new-object "Microsoft.SqlServer.Management.SMO.Server" $serverName
 $srv.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject")
 $db = New-Object "Microsoft.SqlServer.Management.SMO.Database"
 $db = $srv.Databases[$dbname]
 $scr = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
 $deptype = New-Object "Microsoft.SqlServer.Management.Smo.DependencyType"
 $scr.Server = $srv
 $options = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"
 $options.AllowSystemObjects = $false
 $options.IncludeDatabaseContext = $true
 $options.IncludeIfNotExists = $false
 $options.ClusteredIndexes = $true
 $options.Default = $true
 $options.DriAll = $true
 $options.Indexes = $true
 $options.NonClusteredIndexes = $true
 $options.IncludeHeaders = $false
 $options.ToFileOnly = $true
 $options.AppendToFile = $true
 $options.ScriptDrops = $false 

 # Set options for SMO.Scripter
 $scr.Options = $options

 #=============
 # Tables
 #=============
 $options.FileName = $scriptpath + "\$($dbname)_tables.sql"
 New-Item $options.FileName -type file -force | Out-Null
 Foreach ($tb in $db.Tables)
 {
  If ($tb.IsSystemObject -eq $FALSE)
  {
   $smoObjects = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection
   $smoObjects.Add($tb.Urn)
   $scr.Script($smoObjects)
  }
 }

 #=============
 # Views
 #=============
 $options.FileName = $scriptpath + "\$($dbname)_views.sql"
 New-Item $options.FileName -type file -force | Out-Null
 $views = $db.Views | where {$_.IsSystemObject -eq $false}
 Foreach ($view in $views)
 {
   if ($views -ne $null)
   {
    $scr.Script($view)
  }
 }

 #=============
 # StoredProcedures
 #=============
 $StoredProcedures = $db.StoredProcedures | where {$_.IsSystemObject -eq $false}
 $options.FileName = $scriptpath + "\$($dbname)_stored_procs.sql"
 New-Item $options.FileName -type file -force | Out-Null
 Foreach ($StoredProcedure in $StoredProcedures)
 {
   if ($StoredProcedures -ne $null)
   {   
    $scr.Script($StoredProcedure)
  }
 } 

 #=============
 # Functions
 #=============
 $UserDefinedFunctions = $db.UserDefinedFunctions | where {$_.IsSystemObject -eq $false}
 $options.FileName = $scriptpath + "\$($dbname)_functions.sql"
 New-Item $options.FileName -type file -force | Out-Null
 Foreach ($function in $UserDefinedFunctions)
 {
   if ($UserDefinedFunctions -ne $null)
   {
    $scr.Script($function)
  }
 } 

 #=============
 # DBTriggers
 #=============
 $DBTriggers = $db.Triggers
 $options.FileName = $scriptpath + "\$($dbname)_db_triggers.sql"
 New-Item $options.FileName -type file -force | Out-Null
 foreach ($trigger in $db.triggers)
 {
   if ($DBTriggers -ne $null)
   {
     $scr.Script($DBTriggers)
   }
 }

 #=============
 # Table Triggers
 #=============
 $options.FileName = $scriptpath + "\$($dbname)_table_triggers.sql"
 New-Item $options.FileName -type file -force | Out-Null
 Foreach ($tb in $db.Tables)
 {     
   if($tb.triggers -ne $null)
   {
     foreach ($trigger in $tb.triggers)
     {
       $scr.Script($trigger)
     }
   }
 } 
}

#=============
# Execute
#=============
GenerateDBScript $args[0] $args[1] $args[2]

引用自：https://dba.stackexchange.com/questions/31053