在SQL Server数据库项目中更改表脚本 [英] Alter table script in SQL Server database project

问题描述

我的要求是为现有项目创建增量脚本，因此我们将进行很多更改并在其中创建更多表.我们有开发，进入，质量保证，阶段和生产环境.我只想在开发环境中进行更改，其余环境必须由Dacpac使用VSTS自动处理.除种子数据外，所有脚本都必须可重新运行.我能够添加表，但是无法在构建模式下在数据库项目中添加alter table语句.我不想导入完整的数据库.为什么它不接受alter语句.因为我必须检查部署后脚本是否存在，所以我不想在那里使用alter语句.如何实现这一目标.

My Requirement is to create delta script for existing project,So we will be making lot of changes and create more tables in it. We have dev,into,qa , stage and production environment. I want to do the changes only in dev environment and rest of the environment has to be taken care by Dacpac automatically using VSTS. All the scripts has to re runnable except the seed data. I am able to add a table, But unable to add a alter table statement in database project in build mode. I don't want to import full database. Why it's not accepting alter statement. Since I have to check if exists for post deployment script I don't want use alter statement there. how to achieve this.

推荐答案

我认为您可能想念SQL Server数据库项目是什么/做了什么.它自己的项目使用SQL脚本构建数据库外观的内存模型.然后，此模型编译为DACPAC，其中包含描述数据库应该外观的元数据.部署DACPAC时，这会生成一个更改脚本，该脚本会将数据库转换为与DACPAC中描述的模型匹配的状态.

I think you may have miss understood what the SQL Server database project is/does. The project it self uses SQL scripts to build an in memory model of what your database looks like. This model then compiles down to a DACPAC which contains meta data that describes what the database should look like. When you deploy the DACPAC, this generates a change script that will transform the database into a state that matches the model described in the DACPAC.

您的 ALTER TABLE 不起作用的原因是没有可更改的表.该项目不是数据库，也不知道如何在内存中表示您的 ALTER 语句.如果将其包含在部署前或部署后脚本中，则模型将忽略此脚本.如您所知，它将与其他环境的部署混乱.

The reason your ALTER TABLE doesn't work is due to there being no table to alter. The project isn't a database and it doesn't know how to represent in memory your ALTER statements. If you include it in a pre or post deploy script, the model will ignore this. It will, as you found out, mess with the deployments to the other environments.

将数据库部署到 dev 环境的理想方法是通过DACPAC的CI/CD实践使用VSTS.我不确定为什么不希望使用DACPAC部署到您的 dev 环境，但是如果这是一条非常困难的规则，那么您可以在Visual Studio的SSDT中使用架构比较来复制您在本地对目标数据库所做的更改.

The ideal way to deploy your database to your dev environment is using VSTS via CI/CD practices with the DACPAC. I'm not sure why you don't want to use a DACPAC to deploy to your dev environment, but if this is a hard fast rule, then you can use schema compare in Visual Studio's SSDT to copy your changes locally to the target database.

这篇关于在SQL Server数据库项目中更改表脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！