CODEWITHSUNDEEP
sql-serversql-server-data-toolsdacsqlpackage
Mike
Mike

Reputation: 168

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

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.

enter image description here

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?

Upvotes: 8

Views: 2399

Answers (3)

Andrew Wechsler
Andrew Wechsler

Reputation: 11

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

Upvotes: 1

Rodrigo Werlang
Rodrigo Werlang

Reputation: 2176

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.

Upvotes: 1

scar80
scar80

Reputation: 1702

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

Upvotes: 0

Related Questions