CODEWITHSUNDEEP
sqlazure-sql-databasesql-server-data-toolsdacpac
user8179431
user8179431

Reputation:

Alter table script in SQL Server database project

My Requirement is to create delta scripts for an existing project, so we will be making lot of changes and create more tables in it.

We have Dev, QA, Stage and Production environments. I want to do the changes only in dev environment and rest of the environments have to be taken care by Dacpac automatically using VSTS. All the scripts have to be re-runnable except the seed data.

I am able to add a table, but unable to add an alter table statement in database project in build mode. I don't want to import full database. Can the Dacpac not accept alter statements?

Since I have to check if exists for post deployment script, I don't want use alter statement there. How can I achieve this?

Upvotes: 2

Views: 5789

Answers (1)

tj-cappelletti
tj-cappelletti

Reputation: 1864

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.

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.

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.

Upvotes: 6

Related Questions