5

I am using the sqlpackage utility and MSBuild to generate source and target .dacpac files from our database and database projects. I am aware that I can generate publish scripts and even do deployment reports, but these reports are only at a high-level. What I want to do is be able to provide a report that shows me exactly what is different between the two dacpacs. For example, the generated publish script and DeployReport.xml show me that a particular stored procedure has changed and will be overwritten, however it does not show me which lines of the stored procedure have changed, and as it is a very long stored procedure, figuring this out manually takes quite a bit of effort.

So far I've tried extracting the contained model.xml from the .dacpac files and using a Diff tool to compare the model.xml from my source.dacpac to the target.dacpac, but if one is generated from the live database and the other built from a DB project then they are unfortunately much too different to be able to show me a useful visual.

In Visual Studio, I can of course open the "Schema Compare" tool and compare my dacpacs this way, but that is much too manual and I am looking for a more automated solution. A way of triggering this "Schema Compare" from the command line would be nice, at the very least.

What is the best way to compare two dacpac files and show me precisely what the differences are between them at a detailed level? i.e. that these 5 stored procs changed, and show me visually what lines changed in each of the five procs? I am okay with duct-taping together some kind of solution but I cannot purchase any 3rd party tools, I am stuck with what MS provides and my own limited capabilities.

Ryan Lind
  • 51
  • 2

2 Answers2

2

Not a final solution but a possible way to go:

# build publish script from old to new dacpac
SqlPackage.exe /a:Script /sf:old.dacpac /tf:new.dacpac /tdn:mydb /OutputPath:back.sql /Profile:publish.xml
# from new to old
SqlPackage.exe /a:Script /sf:new.dacpac /tf:old.dacpac /tdn:mydb /OutputPath:forth.sql /Profile:publish.xml

# compare them this way or another
Compare-Object -DifferenceObject (Get-Content ./forth.sql) -ReferenceObject (Get-Content ./back.sql) -SyncWindow 5 -IncludeEqual`
 | % { Write-Output ("{0} {1}" -f $_.SideIndicator, $_.InputObject); } | Out-File ./diff.txt
Ivan Starostin
  • 8,798
  • 5
  • 21
  • 39
1

I think you are looking for a wrong tool.
If you want to see a difference between code of a specific stored procedure over time, you should register your DB project in source control (like git or TFS) and then you can get detailed reports what was changed and when.
It will not only give you difference between 2 versions, you will be able to see which developer wrote specific line and when, or aggregate your changes over time.
There is many options how to mark when you created your dacpac in repo and to get detailed report you will ask your repo for a differences between two commits or versions.

You can read about free azure private Git repos here: https://azure.microsoft.com/en-us/services/devops/repos/

Piotr Palka
  • 3,086
  • 1
  • 9
  • 17
  • 1
    we ARE using git. What I am looking for is to be able to compare the stored procedure as it is defined in git to what is actually in the database. – Ryan Lind Sep 17 '20 at 21:12
  • Are you trying to address code drift when some changes are deployed outside of git driven process? Running schema compare and then update git repository from database is a nice semiautomatic way to save drift in repo, and it is like 5 clicks total. Then you can use all repo tools for nice presentation. – Piotr Palka Sep 18 '20 at 05:09