VS2010 数据库项目可以创建可重新运行的部署脚本吗？
我使用 VS2010 数据库项目作为在发布版本之间对数据库代码进行版本控制的一种方式。到目前为止，提供的解决方案似乎不错；但是有一个问题我想知道其他人是否遇到过。
从架构比较视图生成的升级脚本不可重新运行。作为标准，我通常确保所有数据库发布脚本都可以重新运行，以防万一将发布脚本部署到服务器时出现任何问题。
有谁知道是否有一种方法可以配置 VS2010 来创建可重新运行的升级脚本，即仅在存储过程存在时才删除存储过程等？
I’m using VS2010 Database Projects as a way of versioning our database code between release versions. So far, the solution provided seems good; however there is one problem I wonder if anyone else has run into.
The upgrade scripts generated from the schema compare view are not re-runnable. As a standard, I normally ensure that all my database release scripts are re-runnable, just in case there should be any issues deploying the release script to a server.
Does anyone know if there is a way to configure VS2010 to create re-runnable upgrade scripts i.e. drop sprocs only if they exist etc.?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
可能会查看 RedGate 的 Sql Packager
Might check out Sql Packager from RedGate
完成架构比较并将数据库项目模型与数据库同步后，您可以构建它来获取数据库的 .dbschema 文件。
然后可以将其与 vsdbcmd 一起使用来创建更改脚本以升级早期版本的数据库以匹配 .dbschema 文件中定义的版本。这将为您提供可重新运行的升级脚本，此外，如果失败，它会告诉您失败的原因，并且不会对目标数据库进行任何更改。然而，只有当从相同的数据库版本转到相同的数据库版本时，它才可以重用。
我们转向数据库项目（来自 redgate sql 比较生成的脚本）的主要原因之一是为了摆脱这个问题。我们所有的升级脚本都是从特定版本到特定版本。数据库项目的主要好处之一是您不需要可重新运行的脚本。您拥有定义目标应如何结束的 .dbschema 文件，并让 VSDBCMD 完成所有工作。我们甚至不倾向于生成脚本，我们使用 VSDBCMD 直接更新数据库。
如果您沿着这条路线http://blogs.msdn.com/b/bahill/archive/2009/02/21/deploying-your-database-project-without-vstsdb-installed.aspx 有一个列表在开发环境之外运行 VSDBCMD 所需的先决条件。
Once you've done the schema compare and synced your database project model with your database you can build it to get the .dbschema file for your database.
This can then be used with vsdbcmd to create a change script to upgrade an earlier version of the DB to match the version defined in the .dbschema file. This will provide you with your re-runnable upgrade script, plus if it fails it will tell you why it failed and no changes to your target DB will be made. It will, however, only be reuseable when going from the same database version to the same database version.
One of the main reasons we moved to database projects (from redgate sql compare generated scripts) was to get away from this problem. All our upgrade scripts were from a specific version to a specific version. One of the main benefits of DB projects is that you don't need to have re-runnable scripts. You have the .dbschema file which defines how the target should end up, and you let VSDBCMD do all the work. We don't tend to even generate the scripts, we use VSDBCMD to update the database directly.
If you go down this route http://blogs.msdn.com/b/bahill/archive/2009/02/21/deploying-your-database-project-without-vstsdb-installed.aspx has a list of pre-reqs needed to run VSDBCMD outside of your dev environment.
vsdbcmd 的缺点是您必须跳过您的头脑才能将不可为 NULL 的列添加到表中。特别是如果你有大量数据的话。
The drawback of vsdbcmd is that you'll have to jump over your head in order to add non-NULLable columns to a table. Especially if you have a lot of data there.
在 Visual Studio 2015（可能还有早期版本）中，您可以将发布脚本设置为包含具有错误处理功能的单个事务。请参阅下文：
使用“包含事务脚本”进行 SSDT 发布
将普通脚本包装在更大的脚本中事务对我不起作用，因为该脚本在 Visual Studio 中可用的部署前/部署后脚本之外引入了 sqlcmd 错误处理Studio 项目：
这会阻止您使用 try-catch 有效地处理错误，并使您必须手动回滚事务
注意：部署前/部署后脚本在“包含事务”生成的事务外部运行脚本”设置，因此您必须自己管理这些
In Visual Studio 2015 (and possibly earlier versions) you can set the publish script to include a single transaction with error handling. See below:
SSDT publishing with "Include Transactional Scripts"
Wrapping the normal script in a larger transaction wouldn't work for me because the script introduces sqlcmd error handling outside of the pre-/post-deployment scripts that are available within the Visual Studio project:
This stops you using try-catches to handle errors effectively and leaves you having to rollback the transaction manually
Note: the pre-/post-deployment scripts run outside the transaction generated by the "Include Transactional Scripts" setting, so you will have to manage these yourself