SSDT SQL Server数据库项目中模式比较的命令行/API?[英] Command-line/API for Schema Compare in SSDT SQL Server Database Project?

本文是小编为大家收集整理的关于SSDT SQL Server数据库项目中模式比较的命令行/API?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

在2012年Visual Studio中,我们有 <<< ssdt 's SQL Server Database Project(dbproject) )项目有助于

  1. 比较source vors target
  2. 更新target使其与source
  3. 相同

其中

  1. 源和目标可以是数据库,DBProject项目,也可以是.dacpac文件
  2. 更新可以通过更新操作或生成的脚本

我的问题是可以拥有,在哪里可以获得命令行/API接口来调用此功能?

推荐答案

源数据库

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

目标数据库

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

比较和生成delta脚本

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

执行脚本

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

我在CMD脚本中执行此操作,因为我们的IT部门将不允许使用未签名的PowerShell脚本,并且他们不会购买证书.即使从TFS 2012 Team构建或简单地从vs命令提示中执行.cmd脚本时,这也可以完美无缺.

注意!

在您的脚本中添加以下设置:SET PATH=%PATH%;C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin

也:您可以看到我设置了路径变量.我这样做是因为我正在触摸数据库的多达50个口味,这是我发现生成三角洲脚本并更新我们的开发和测试数据库的唯一一致的方法.

其他推荐答案

目前,让API访问架构比较结果的唯一方法是编写在DACServices API中部署/脚本操作期间运行的部署计划修饰符/执行程序.这使您可以检查将DACPAC与数据库进行比较时生成的部署计划,但它还可以访问 ModelComparisonResult 代表模式比较操作,该操作可在上下文对象中可用,传递给 onexecute 贡献者的方法.我刚刚写过博客发布涵盖此过程,可能对您有用 - 看看它,希望它可以帮助您开始.

其他推荐答案

i 写了几行在此主题上.希望您发现它们有用.

在简而

本文地址:https://www.itbaoku.cn/post/597548.html

问题描述

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?

推荐答案

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.

其他推荐答案

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.

其他推荐答案

I wrote a few lines on this topic a while ago. Hopefully you find them useful.

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