Skip to main content

Create a dacpac To Compare Schema Differences


It's been some time since i added anything to the blog and a lot has happened in the last few months.
I have run into many number of challenging stuff at Xero and spread my self to learn new things.

As a start i want to share a situation where I used a dacpac to compare the differences of a database schema's.
- This involves of creating the two dacpacs for the different databases
- Comparing the two dacpacs and generating a report to know the exact differences
- Generate a script that would have all the changes


How to generate a dacbpac
The easiest way to create a dacpac for a database is through management studio ( right click on the databae --> task --> Extract data-tier-application). This will work under most cases but will error out when the database has difffrent settings. ie. if CDC is enabled

To work around this blocker, you need to use command line to send the extra parameters. Bellow is the command used to generate the dacpac.


"%ProgramFiles(x86)%\Microsoft SQL Server"\110\DAC\bin\sqlpackage.exe /Action:Extract /SourceServerName:XLW-CNU415CD8B /SourceDatabaseName:AdventureWorks2012 /TargetFile:AdventureWorks2012_v1.dacpac /p:IgnoreExtendedProperties=True /p:IgnorePermissions=False /p:ExtractApplicationScopedObjectsOnly=True

Note
- In this case i have created the dacpac against the AdventureWorks2012 database and called the dacpac AdventureWorks2012_v1.dacpac
- The extra parameters that i have passed start with "/p", you can read more on SQLPackage.exe @ https://msdn.microsoft.com/library/hh550080(v=vs.103).aspx


Compare the dacpacs
Now that i have the dacpac in place, its a matter of identifying the differences. Being the pessimistic person i am, I preferred to view the a list of changes before they were applied on the destination database. In-order to that I would request a deployreport instead of a script.

"\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" /a:DeployReport /sf:AdventureWorks2012_v1.dacpac /tf:AdventureWorks2012_v2.dacpac /tdn:AdventureWorks2012.db /op:AdventureWorks2012_v1.xml

Above I have requested for the deploy report in xml, and used the AdventureWorks2012_v1.dacpac and AdventureWorks2012_v2.dacpac for the comparison.


Similarly , just by changing the action to script, the .sql file can be generated

"\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" /a:Script /sf:AdventureWorks2012_v1.dacpac /tf:AdventureWorks2012_v2.dacpac /tdn:AdventureWorks2012.db /op:AdventureWorks2012_v1.sql



As usual, i hope this helps someone in the big world of sql.

Comments

  1. UnknownJuly 6, 2015 at 7:26 AM

    Your post is very useful. I have one doubt.. The sql script generated after comparing two dacpac files cannot be executed direclty in SSMS?

    ReplyDelete
    Replies
    1. Jude PieriesJuly 10, 2015 at 1:53 PM

      Hi Ramya

      Yes you are right, you can't directly execute it on default setting on SSMS. It requires that you change the SSMS mode to SQLCMD.

      Cheers
      Jude

      Delete
  2. AnonymousJanuary 19, 2017 at 1:58 PM

    Hi Jude, Is there anyway to insert If EXIST check when generating script after comparing two Dacpac?

    ReplyDelete
    Replies
    1. UnknownJanuary 30, 2017 at 12:40 PM

      Hi

      As much as i know there no way to do that. That said yu should be able to use the additional parameters and unnecessary data.
      Hope that make sense.

      Thanks
      Jude

      Delete
  3. AnonymousJanuary 31, 2017 at 1:19 AM

    When tried to compare dcapac's i got the error
    *** An error occurred during deployment plan generation. Deployment cannot continue.
    Missing values for the following SqlCmd variables:ClientName.

    ReplyDelete
    Replies
    1. UnknownFebruary 9, 2017 at 12:29 PM

      v/:ClientName=

      you might get more warnings with missing variables,Just add them.
      FYI These variables are set in your project

      Delete
  4. UnknownFebruary 9, 2017 at 12:34 PM

    I got this error when comparing a 2014 vs 2012 dacpac ;

    *** An error occurred during deployment plan generation. Deployment cannot continue.
    A project which specifies SQL Server 2014 as the target platform cannot be published to SQL Server 2012.

    Solution
    Just flip the dacpacs for /sf and /tf

    ReplyDelete
  5. AnonymousApril 20, 2017 at 2:39 AM

    I got this issue can some one help me

    SqlPackage.exe : *** An error occurred during deployment plan generation. Deployment cannot continue.
    At D:\CompareDacPac.ps1:55 char:9
    + & "$SqlPackagePath" /a:script /tf:$v2 /sf:$SourceDACPAC /tdn: ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (*** An error oc...annot continue.:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError

    ReplyDelete
    Replies
    1. Jude PieriesApril 26, 2017 at 4:02 PM

      have you tried removing the double quotes on either side of the $SqlPacjkagepath ? it does appear to be the way the PS variable is used.

      Delete

Post a Comment

Popular posts from this blog

High Watermarks For Incremental Models in dbt

The last few months it’s all been dbt. Dbt is a transform and load tool which is provided by fishtown analytics. For those that have created incremental models in dbt would have found the simplicity and easiness of how it drives the workload. Depending on the target datastore, the incremental model workload implementation changes. But all that said, the question is, should the incremental model use high-watermark as part of the implementation. How incremental models work behind the scenes is the best place to start this investigation. And when it’s not obvious, the next best place is to investigate the log after an test incremental model execution and find the implementation. Following are the internal steps followed for a datastore that does not support the merge statements. This was observed in the dbt log. - As the first step, It will copy all the data to a temp table generated from the incremental execution. - It will then delete all the data from the base table th
4 comments
Read more

How To Execute A SQL Job Remotely

One of the clients needed its users to remotely execute a SQL job and as usual I picked this up hoping for a quick brownie point. Sure enough there was a catch and there was something to learn. Executing the job through SQLCMD was a no-brainer but getting it to execute on the remote machine was bit of challenge. On the coding Front 1    1.)     The bat file included the following code                 SQLCMD -S "[ServerName] " -E -Q "EXEC MSDB.dbo.sp_start_job @Job_Name = ' '[JobName]" 2    2.)     The Individual users were given minimum permissions  to execute the SQL job Ex. use msdb EXECUTE sp_addrolemember @rolename = 'SQLAgentOperatorRole', @membername = ' Domain\UserLogin ' At the client machine              This took a fair bit of time till our sysadmin got me an empty VM machine.  Thanks Michael G                   I’m just going to copy the exact instructions that I copied to OneNote and passed on
Post a Comment
Read more