如何將多個數據庫的模式與真實副本進行比較？

我們為每個客戶都有一個數據庫，超過 700 個數據庫。所有模式都應該完全相同。我們有原始碼控制數據庫，並希望將架構與 700 進行比較。有時，生產中的架構會脫節/偏離實際的原始碼控制大師。

如何將來自多個數據庫的架構與 Visual Studio SSDT 項目或生產中的黃金副本空數據庫進行比較？

我查看了各種可能不是最佳的技術。

與 SSDT 項目比較

此人使用遞歸腳本：Schema 一次比較多個數據庫

然而作者利用文件大小，我不喜歡作為差異指標。

‘我比較文件大小的技巧是通過在它們所在的文件夾上使用 Shift-RightClick 打開一個 CMD 視窗，選擇’在此處打開命令視窗’，然後執行’DIR’，它將列出文件大小（以字節為單位） . '

提取黃金.dacpac：

sqlpackage.exe /a:Extract /scs:Server=MyLaptopSQL2014;Database=Test; /tf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac

Compare to the target databases:  
sqlpackage.exe /a:Script /sf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac /tsn:MyLaptopSQL2014 /tdn:Test1 /op:C:UsersKevin3NFDocumentsSQLScriptsDACPACSDeltasTest1.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True 
sqlpackage.exe /a:Script /sf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac /tsn:MyLaptopSQL2014 /tdn:Test2 /op:C:UsersKevin3NFDocumentsSQLScriptsDACPACSDeltasTest2.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True 
sqlpackage.exe /a:Script /sf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac /tsn:MyLaptopSQL2014 /tdn:Test3 /op:C:UsersKevin3NFDocumentsSQLScriptsDACPACSDeltasTest3.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True 
sqlpackage.exe /a:Script /sf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac /tsn:MyLaptopSQL2014 /tdn:Test4 /op:C:UsersKevin3NFDocumentsSQLScriptsDACPACSDeltasTest4.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True 
sqlpackage.exe /a:Script /sf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac /tsn:MyLaptopSQL2014 /tdn:Test5 /op:C:UsersKevin3NFDocumentsSQLScriptsDACPACSDeltasTest5.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True 

與生產中的黃金副本數據庫相比：

此技術不查看預設指標或索引列名稱、觸發器、儲存過程等。

比較 SQL Server 表架構和數據的不同方法

那麼將模式與許多數據庫進行比較的最佳方法是什麼？我不需要知道結果，我只需要一天結束時的標誌指示器，不同的 Y/N。最好使用 DMV 方法並在一個簡單的表格中顯示是/否結果，但 SSDT 也可以工作。

這將比較數據庫腳本。不想使用 sql compare，因為它可能會在伺服器上花費額外的模式鎖定時間。

#Set-ExecutionPolicy RemoteSigned


#OriginalDatabaseScript, this can be generate from source control or Master Copy database in Production
$OriginalScript = 'C:\DatabaseCompare\OriginalScript.sql'

#Source: Place in test file below, list databases by running select * from sys.databases where database_id > 4
$DatabaseSourceList = 'C:\DatabaseCompare\DatabaseList.txt'

#Destination: Database Files Generate Scripts
$Filepath='C:\DatabaseCompare\scripts' # local directory to save build-scripts to


foreach($line in Get-Content $DatabaseSourceList) 
{

$DataSource='localhost' # server name and instance
$Database=$line # the database to copy from
# set "Option Explicit" to catch subtle errors
set-psdebug -strict
$ErrorActionPreference = "stop" # you can opt to stagger on, bleeding, if an error occurs
# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and 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" #
$s = new-object ("$My.Server") $DataSource
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") 
$CreationScriptOptions.ExtendedProperties= $true # yes, we want these
$CreationScriptOptions.DRIAll= $true # and all the constraints 
$CreationScriptOptions.Indexes= $true # Yup, these would be nice
$CreationScriptOptions.Triggers= $true # This should be included when scripting a database
$CreationScriptOptions.ScriptBatchTerminator = $true # this only goes to the file
$CreationScriptOptions.IncludeHeaders = $false; # of course
$CreationScriptOptions.ToFileOnly = $true #no need of string output as well
$CreationScriptOptions.IncludeIfNotExists = $true # not necessary but it means the script can be more versatile
$CreationScriptOptions.Filename =  "$($FilePath)\$($Database)_Build.sql"; 
$NewFilename =  "$($FilePath)\$($Database)_Build2.sql"; 

$transfer = new-object ("$My.Transfer") $s.Databases[$Database]

$transfer.options=$CreationScriptOptions # tell the transfer object of our preferences
$transfer.ScriptTransfer()
"Created Database " + $Database

#Get-Content $CreationScriptOptions.Filename | Where { $_ -notmatch "/" } | Set-Content $NewFilename



if(Compare-Object -ReferenceObject $(Get-Content $OriginalScript) -DifferenceObject $(Get-Content $NewFilename))
   {$Database + " Files are different"}
Else 
   {$Database + "Files are the same"}


}

從這裡編輯： 使用 Powershell 和 SMO 自動生成腳本

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