Export SqlDatabase to Bacpac using SqlPackage.exe fails on fillfactor

Question

I am using the following command to execute sqlpackage to export a bacpac for a local db.

"C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /action:Export /tf:".\dbname_Baseline.bacpac" /SourceConnectionString:"XXXXXXXXXXXX"

It fails saying "Element Primary Key: [dbo].[PK_Name] has an unsupported property FillFactor set and is not supported when used as part of a data package."

I understand fillfactor is not supported but is there anything to make it ignore unsupported properties?

Thanks

Mark

Lighten

Try updating the DacFx and SMO packages from nuget or install the newest SSMS. That should update the necessary dll's for I/E in your GAC. – Temple

Answer 1

Pass the switch

/p:IgnoreFillFactor ={ True | False }

Blankbook

Hi, I get 'IgnoreFillFactor' is not a valid argument for the 'Export' action. when I try to put this argument to my command line. – Dogvane

This looks like a parameter for /action:Publish instead of /action:Export. (getting a db ready for sql azure is fun isn't it?) – Inspissate

Answer 2

I have been experimenting with exporting to bacpac for a small database of around 1GB and you can reconfigure the fillfactor in the database. In SQL Server Manager Studio, just script a 'drop and re-create' for the index, edit the generated script to remove the fillfactor specification, run the script and this re-creates the index with a default fill factor acceptable to sqlpackage.exe. This should work for PKs as well as indexes, it should be quick for small databases and should have no adverse effect on the database.

I realize this doesn't answer the question about making sqlpackage.exe ignore some errors, but it is probably a better way of fixing the problem as it does so directly in the database.

Automatism

This is a valid solution for small databases. However, we've got a large database with 270+ problem indexes! There has to be a better solution to this! – Champaigne

To expand my answer you can also change the fillfactor using the ALTER INDEX REBUILD command, you don't have to use the SQL Server Manager Studio GUI. You could write a script to rebuild the indexes one by one with the required fill factor, and you could run the changes over a period of time. Once an index is rebuilt with the required fillfactor it should not need to be rebuilt again. Also I believe SQL Server Enterprise edition allows ALTER INDEX REBUILD to occur online, which should reduce the downtime. – Automatism