39

In Visual Studio 2012, we have Schema Compare in SSDT's SQL Server Database Project (DbProject) project which helps

  1. Compare source versus target
  2. Update target to make it the same as source

Where

  1. Source and target can be either a database, a DbProject project, or a .dacpac file
  2. Update can be done via an update action or generated script

My question is that is it possible to have and where can I get the command-line/API interface to call this feature?

Improve this question

3 Answers 3

Reset to default
61

SOURCE Database

sqlpackage.exe /a:Extract /scs:Server=%Server%;Database=AspBaselineDB; /tf:%DriveSpec%\%DacPath%\%AspBaselineDB%_baseline.dacpac

TARGET Database

sqlpackage.exe /a:Extract /scs:Server=%Server%;Database=%AspTargetDB-2%; /tf:%DriveSpec%\%DacPath%\%AspTargetDB%.dacpac

COMPARE & GENERATE the Delta script

sqlpackage.exe /a:Script /sf:%DriveSpec%\%DacPath%\%AspBaselineDB%_baseline.dacpac /tf:%DriveSpec%\%DacPath%\AspNetDb\%AspTargetDB%.dacpac /tdn:aspTargetdb /op:%DriveSpec%\%SqlPath%\AspNetDb\AspDbUpdate.sql

EXECUTE the script

sqlcmd.exe -S %Server%\aspnetdbAmexDev -i %DriveSpec%\%SqlPath%\AspNetDb\AspDbUpdate.sql

I do this in CMD scripting as our IT dept will not allow unsigned PowerShell scripts and they won't purchase a cert. This works flawlessly, even when calling it from TFS 2012 Team Builds or simply executing the .CMD script from a VS command prompt as Administrator.

Note!

Add the following SET in your script: SET PATH=%PATH%;C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin

Also: as you can see I set path variables. I do this as I am touching up to 50 flavors of the database and this is the only consistent way I have found to generate delta scripts and update our DEV and TEST databases.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Can we also compare specific user table(s) data and generate the script?
is there any way to do this for a certain table, or item? Schema Compare in Visual Studio and Update will allow people to manually pick and choose, how do I do this in command line?
3

At present, the only way to get API access to schema compare results is by writing a deployment plan modifier/executor that runs during a deploy/script operation in the DacServices API. This lets you examine the deployment plan generated when comparing a dacpac against a database, but it also gives access to the ModelComparisonResult that represents a schema compare operation, which is available in the context object passed to the OnExecute method of a contributor. I've just written a blog post that covers this process and might be useful to you - take a look at that and hopefully it'll help you get started.

Improve this answer

1 Comment

Need a nice powershell one liner to do this :)
1

In a nutshell, the tool you are looking for is vsdbcmd.exe, which is distributed by Ultimate and Premium versions of Visual Studio.

Improve this answer

6 Comments

Cool! This seems to be the one I need.
I'm using Visual Studio 2012. VSDbCmd.exe seems to be for Visual Studio 2010 right?
It is unlikely to be available for Visual Studio 2012 as mentioned here social.msdn.microsoft.com/Forums/en-US/…
SqlPackage is an updated version of vsdbcmd.exe. It has equivalent functionality. @NamG.VU - you said in response to a previous answer that SqlPackage wouldn't help, but here you say that vsdbcmd would? What exact feature do you need that SqlPackage doesn't have?
@Nam I wasn't aware they had removed vsdbcmd from vs2012. Hopefully you can perform the same operation and mechanics using SqlPackage which at first glance seems to be pretty similar (msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx)
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.