最近工作需要比對前次幫客戶建置的資料庫，並將新專案的資料表補齊，但因為無法直接操作客戶的正式機，且前人並沒有對前次建置的資料庫進行合式的紀錄，因此阿猩這幾天整理了一些可以完成工作的作法。
資料層應用程式
資料層應用程式(Data-tier Application - DAC)
資料層應用程式包含所有SQL Server物件，包含Table、View、Stored Procedure等，又可依內容分為兩種檔案類型
- .dacpac：僅資料庫結構描述
- .bacpac：資料庫結構描述 + 資料
如果要還原整個資料庫或是新增少量Table及資料，使用.bak或產生Insert指令碼會方便許多。但目前阿猩遇到的問題是，正式機已上線一陣子，如果使用.bak會有問題，要新增的Table，又必須先跟正式機比對後，才能確定哪些Table或資料需要新增或異動，接著讓我們來看看如何利用資料層應用程式來解決這部分的問題。
取得.dacpac檔案流程
人算不如天算
Error SQL71501
正當阿猩很開心，開始將所有資料庫都匯出結構描述時，好幾個資料庫居然匯出失敗，錯誤訊息會像是
Error SQL71561: 驗證元素 [dbo].[vwTest].[Memo] 時發生錯誤: 計算資料行: [dbo].[ vwTest].[Memo] 包含 物件 [DiffCompare].[dbo].[Test].[Memo] 無法解析的參考。原因是，進行匯出結構描述時，會比對Table欄位是否一致，如果不一致，就會發生Error SQL71501錯誤訊息。在阿猩公司的問題就是，某個View使用了Memo欄位進行篩選，但在[DiffCompare].[dbo].[Test] Table中找不到Memo欄位了，只要將兩邊統一後，這個問題就解決了。
Error SQL71561
另一個資料庫遇到錯誤代碼是Error SQL71561，檢驗欄位無誤後，阿猩開始懷疑人生，這個問題是因為，在A資料庫中建立View，而View使用的是B資料庫的Table，因此程式無法比對兩者Schema，後來找到了SqlPackage 來解決這個問題(參考1)。
SqlPackage
下載並安裝SqlPackage(參考2)，目前預設安裝路徑是「C:\Program Files\Microsoft SQL Server\160\DAC\bin」，執行
SqlPackage.exe /Action:extract /SourceServerName:$ServerName /SourceDatabaseName:$DbName /tf:"C:\Users\xxx.dacpac
Action：exreact 表示要執行匯出的動作
SorceServerName：將$ServerName改為要使用的Server，如localhost
SorceDatabaseName：將$DbName改為要匯出的資料庫
tf：目標路徑及檔名
因SqlPackage不會對引用的資料庫進行Schema檢查，就可以成功取得.dacpac檔啦，但事後還是要人工檢查一下，兩邊Schema是否相同。
產出Insert Into語法
在成功建立所有Table之後，有些資料是屬於固定參數，因此也得將資料寫進正式機資料庫內，這裡阿猩試過過2種方法，可以快速產出Insert語句
- SSMS內建的「產生指令碼」
- SQLDumper
如果想用Table篩選，並寫入全部資料，產生指令碼是個不錯的選擇，如果想針對Table中，篩選特定資料，SQLDumper可以下Where指令會更方便，有興趣自行Google。
參考資料