search

microsoft / DacFx

DacFx, SqlPackage, and other SQL development libraries enable declarative database development and database portability across SQL versions and environments. Share feedback here on dacpacs, bacpacs, and SQL projects.
https://aka.ms/sqlpackage-ref
MIT License
310 stars 20 forks source link

Bacpac file won't restore due to columnstore indexes on columns with specific data types. #475

Open dzsquared opened 1 month ago

dzsquared commented 1 month ago

from https://feedback.azure.com/d365community/idea/c7ea15d6-364b-ef11-b4ac-000d3a7b1c7e

Situation: We are using Serverless Azure SQL Databases as projects. Afterwards we delete these databases but before we do that we create backups of the databases. We are using SqlPackage.exe (version: 162.3.566) to create bacpac files. Restoring bacpac files back to our Azure subscription works fine, except for following situations: When a database contains a clustered columnstore index on a table that contains following datatypes: varchar(max) , nvarchar(max), or varbinary in this situation we can successful create bacpacs but we are NOT able to restore the bacpacs. It gives following errors: Error SQL72014: Framework Microsoft SqlClient Data Provider: Msg 1919, Level 16, State 1, Line 1 Column 'vcharmax' in table 'dbo.Table_ColumnstoreIndex_01' is of a type that is invalid for use as a key column in an index. Error SQL72045: Script execution error. The executed script: CREATE CLUSTERED INDEX [CCI_Table_ColumnstoreIndex_01] ON [dbo].Table_ColumnstoreIndex_01; CREATE CLUSTERED COLUMNSTORE INDEX [CCI_Table_ColumnstoreIndex_01] ON [dbo].[Table_ColumnstoreIndex_01] WITH (DROP_EXISTING = ON); The same error appears when using nvarchar(max) and varbinary.

It seems that something goes wrong on the tool side when the BACPAC is created or restored. This statement: CREATE CLUSTERED INDEX [CCI_Table_ColumnstoreIndex_01] ON [dbo].Table_ColumnstoreIndex_01; should not be there. Since the data type varchar(MAX) is not supported for rowstore clustered index, this statement gives an error. But would the data type be a supported one, the next statement to create the clustered columnstore index would fail, since you can only have one clustered index on a table. In my opinion the restore statement should be plain and simple: CREATE CLUSTERED COLUMNSTORE INDEX [CCI_Table_ColumnstoreIndex_01] ON [dbo].[Table_ColumnstoreIndex_01]

I understand that we should prevent making clustered columnstore indexes on these kind of columns, but why is it in the first place allowed to make these indexes that gives us issues in our restoring process of BACPAC files.

Anyone experiencing the same issues or know how to fix this problem?

BTW: This cmd is used at restoring the bacpac,: SqlPackage /Action:Import /SourceFile:"F:\db\bacpacs\restoretestdb01.bacpac" /UniversalAuthentication:True /TargetConnectionString:"Server=tcp:servername.database.windows.net,1433;Initial Catalog=restoretestdb01;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=90;"

MarkieMark73 commented 3 weeks ago

Hi Drew, Did you hear any updates on the bug report. And if it is planned to be fixed in upcoming releases? Thanks in advance!