Is there any way to stop SqlPackage.exe setting default Filegroup in deployment script?

Question

We are using Sql Server database projects to create deployment scripts from DacPac using SqlPackage.exe. We have different SQL Server Filegroups setup in various environments. When deploying we exclude Filegroups as we want objects to create in the default Filegroup. In database project settings the default Filegroup is not changed from PRIMARY.

This presents a problem when attempting to deploy to an environment where the default 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 generating in the deployment SQL?

Mulderig

Answer 1

I don't know if you can disable it within SSDT.

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

Devland

Answer 2

When you generate the dacpac file from SSDT in SQL Server Management Studio you can't choose you type of objects you extract to the dacpac.

But when you run sqlpackage.exe via command line there is a parameter you could use.

sqlpackage.exe your_deploy_options /p:ExcludeObjectTypes=Filegroups

This will ignore the deployment of filegroups. See this reference for the full options: https://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx

If you have Visual Studio, then you can import your database as a database project in VS and there remove the filegroups from the scripts, so when you deploy/compare schemas they wouldn't be compared.

Magnitude

Thanks for your answer Rodrigo but unfortunately this does not remove the modify statement. – Mulderig

This did not work for me either. I am trying to remove ALTER DATABASE - MODIFY FILEGROUP [Filegroupname] DEFAULT; line in my sqlpackage script. – Usurious

Answer 3

It can be removed, as I faced this identical issue. You need to exclude Database options.

Excluding Filegroups from the object types deployed will only prevent changes to the Filegroups. Setting a default filegroup is more specifically a database option. While there is a switch to not script database options, an additional switch to exclude DatabaseOptions from the object types is also needed to remove this alter statement you described.

sqlpackage.exe /Properties:ExcludeObjectTypes="DatabaseOptions;Filegroups;Files" /Properties:ScriptDatabaseOptions=False

Unite