I have a question regarding Data-Tier Application (DACPAC) upgrade when deploying to a SQL Azure database. When we upgrade the DACPAC manually through the wizard UI, there is a step where we review the data-loss warning report and have the ability to save the action report to an HTML file (See here under "Review the Upgrade Plan Page"). The Action column displays the actions, such as Transact-SQL statements, that will be run to perform the upgrade. The Data Loss column will contain a warning if the associated action could delete data.
Right now, I'm automating the database upgrade process using Powershell, which works beautifully so far. Unfortunately, I couldn't find a way for it to generate the same data-loss warning report.
An excerpt of my Powershell upgrade script is below:
## Generate the database change list (database drift) and upgrade script and save them to file.
$dacChanges = $dacStore.GetDatabaseChanges($dataTierAppNameToUpgrade) | Out-File -Filepath .\DatabaseChanges.txt
## Getting the DAC incremental upgrade script for data-tier application
$dacStore.GetIncrementalUpgradeScript($dataTierAppNameToUpgrade, $nextDacType) | Out-File -Filepath .\DatabaseUpgrade.sql
The DatabaseChanges.txt output file generated by GetDatabaseChanges() wasn't really informative, so we are wondering if there's a way to get the same report file as the one we would get if we were to go through the upgrade wizard manually. This report has been a great help to the deployment team when resolving data migration issues, and we would like to be able to inspect it manually when we deploy to a live production database.
We've searched through the MSDN documentations but didn't have any luck. Does anyone know if this feature is supported for Powershell deployments? Is it a plan that this will be supported in the near future?
Thanks for your help in advance.
Just got in touch with the owner of DAC @ Microsoft, and here's the solution he suggested: it is possible by using their managed API or through SqlPackage.exe.
SqlPackage.exe has an action on it to produce a deployment report. If there is a possible data loss issue detected it will be included in the report.
Reference: http://msdn.microsoft.com/en-us/library/hh550080(v=VS.103).aspx