Is there a way to stop SqlPackage.exe by setting Filegroup in the script deployment by default?

Question

We use Sql Server database projects to create deployment scripts with DacPac using SqlPackage.exe. We have different settings for SQL Server filegroups in different environments. During deployment, we exclude FileGroups, since we want objects to be created in the filegroup by default. In the database project settings, the standard filegroup does not change from PRIMARY.

This creates a problem when trying to deploy an environment in which the standard filegroup is not PRIMARY because the following code is included ...

ALTER DATABASE [$(DatabaseName)] MODIFY FILEGROUP [PRIMARY] DEFAULT; 

Is there a way to prevent this from being created in SQL deployment?

Answer 1

I do not know if it is possible to disable it in SSDT.

But you can use a member of the deployment plan to filter out the MODIFY FILEGROUP statement or change it the way you want. Detailed instructions and working code can be found here: http://scardevblog.blogspot.com/2015/03/ssdt-generates-2012-option-for-target.html

Answer 2

When you create a dacpac file from SSDT in SQL Server Management Studio, you cannot select the type of objects you extract for dacpac.

But when you run sqlpackage.exe through the command line, you can use the parameter.

sqlpackage.exe your_deploy_options / p: ExcludeObjectTypes = Filegroups

This ignores filegroup deployments. See this link for full options: https://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx

If you have Visual Studio, you can import your database as a database project in VS and remove the filegroups from the scripts, so when deploying / comparing schemas, they will not be compared.

Answer 3

/ p: ExcludeObjectTypes = Filegroups. This option does not help us and does not relieve changes during the termination of the creation or modification of a single file group. I have the same requirement, 16 filegroups, and I only need data on the main filegroup.