During build we generate dacpac files of our database based on a SSDT .sqlproject. This dacpac later gets deployed to production using sqlpackage. Despite using the /p:DropStatisticsNotInSource=False switch, sqlpackage will drop all statistics, that were added after the last sync of the sqlproject with the production database.
We can also reproduce this using a publish profile and the generate script option of SSDT:
<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="14.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<PropertyGroup>
<IncludeCompositeObjects>True</IncludeCompositeObjects>
<TargetDatabaseName>hotel</TargetDatabaseName>
<DeployScriptFileName>Database.sql</DeployScriptFileName>
<TargetConnectionString>connectionstring</TargetConnectionString>
<BlockOnPossibleDataLoss>False</BlockOnPossibleDataLoss>
<DropObjectsNotInSource>True</DropObjectsNotInSource>
<DoNotDropDatabaseRoles>True</DoNotDropDatabaseRoles>
<DoNotDropDatabaseScopedCredentials>True</DoNotDropDatabaseScopedCredentials>
<DoNotDropUsers>True</DoNotDropUsers>
<DoNotDropServerRoles>True</DoNotDropServerRoles>
<DoNotDropSecurityPolicies>True</DoNotDropSecurityPolicies>
<DoNotDropSearchPropertyLists>True</DoNotDropSearchPropertyLists>
<DoNotDropPermissions>True</DoNotDropPermissions>
<DoNotDropPartitionSchemes>True</DoNotDropPartitionSchemes>
<DoNotDropPartitionFunctions>True</DoNotDropPartitionFunctions>
<DoNotDropExternalFileFormats>True</DoNotDropExternalFileFormats>
<DoNotDropExternalTables>True</DoNotDropExternalTables>
<DoNotDropErrorMessages>True</DoNotDropErrorMessages>
<DoNotDropDefaults>False</DoNotDropDefaults>
<ProfileVersionNumber>1</ProfileVersionNumber>
<DropStatisticsNotInSource>False</DropStatisticsNotInSource>
<ScriptRefreshModule>False</ScriptRefreshModule>
</PropertyGroup>
</Project>
How can we force sqlpackage not to drop the statistics?
The problem is the use of
DropObjectsNotInSource=True
, it overwrites theDropStatisticsNotInSource=False
option. This is either a bug or is not specified on thesqlpackage.exe
documentation.One possible workaround is to use AgileSqlClub SSDT filter by Ed Elliott as explained in this blog. In this case you would need to use the
AgileSqlClub.SqlPackageFilter.dll
and add following option:/p:AdditionalDeploymentContributors=AgileSqlClub.DeploymentFilterContributor /p:AdditionalDeploymentContributorArguments="SqlPackageFilter=IgnoreType(Statistics)"