Import BACPAC fails if schema has a large number of tables, indexes and/or views

Published Jun 15 2021 02:53 PM 1,094 Views
Microsoft
Azure portal Import from BACPAC fails if database schema is large because the number of tables, columns, indexes and views.

 

" BadRequest ErrorMessage: The ImportExport operation with Request Id 'ed049000-1d74-46bf-9dd6-2375c5d079fa' failed due to 'An internal error occurred. Please contact Microsoft support and reference the server name, database name, and operation ID.'.."  

 

SQL Server Management Studio "Import data-Tier application" option fails after some time with an error indicating "not enough memory"

 

If the number of tables, indexes and views is large, import model preparation needs a lot of memory and time.
 
In this case the solution is use SqlPackage.exe with "/p:Storage=File" option 

 

/p: Storage=({File|Memory}) Specifies how elements are stored when building the database model. For performance reasons the default is InMemory. For large databases, File backed storage is required.


Syntax

 

sqlpackage.exe /Action:Export /ssn:tcp:<servername>.database.windows.net,1433 /sdn:<source-database-name>  /su:<username> /sp:<Password> /tf:.\export.bacpac /p:Storage=File /d:true /df:.\outputlog.log /p:LongRunningCommandTimeout=0

 

SqlPackage.exe /Action:Import /sf:.\bacpacfile.bacpac /tsn:<servername>.database.windows.net /tdn:<database-name> /p:Storage=File /tu:<username> /tp:********** /d:true /df:.\outputlog.log /p:LongRunningCommandTimeout=0

 

Note that some import steps will take some time (hours) and you could think that process is hang. You can use Windows "Resource Monitor" and monitor Sqlpackage.exe disk activity.
 
Note: This solution also applies to export operations

 

 
Regards, Paloma.-
 
%3CLINGO-SUB%20id%3D%22lingo-sub-2451229%22%20slang%3D%22en-US%22%3EImport%20BACPAC%20fails%20if%20schema%20has%20a%20large%20number%20of%20tables%2C%20indexes%20and%2For%20views%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2451229%22%20slang%3D%22en-US%22%3E%3CDIV%3E%3CSPAN%3EAzure%26nbsp%3Bportal%26nbsp%3BImport%26nbsp%3Bfrom%26nbsp%3BBACPAC%26nbsp%3Bfails%26nbsp%3Bif%26nbsp%3Bdatabase%26nbsp%3Bschema%26nbsp%3Bis%26nbsp%3Blarge%26nbsp%3Bbecause%26nbsp%3Bthe%26nbsp%3Bnumber%26nbsp%3Bof%26nbsp%3Btables%2C%20columns%2C%26nbsp%3Bindexes%26nbsp%3Band%26nbsp%3Bviews.%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%3CSPAN%3E%22%26nbsp%3BBadRequest%26nbsp%3BErrorMessage%3A%26nbsp%3BThe%26nbsp%3BImportExport%26nbsp%3Boperation%26nbsp%3Bwith%26nbsp%3BRequest%26nbsp%3BId%26nbsp%3B'ed049000-1d74-46bf-9dd6-2375c5d079fa'%26nbsp%3Bfailed%26nbsp%3Bdue%26nbsp%3Bto%26nbsp%3B'An%26nbsp%3Binternal%26nbsp%3Berror%26nbsp%3Boccurred.%26nbsp%3BPlease%26nbsp%3Bcontact%26nbsp%3BMicrosoft%26nbsp%3Bsupport%26nbsp%3Band%26nbsp%3Breference%26nbsp%3Bthe%26nbsp%3Bserver%26nbsp%3Bname%2C%26nbsp%3Bdatabase%26nbsp%3Bname%2C%26nbsp%3Band%26nbsp%3Boperation%26nbsp%3BID.'..%22%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%3CSPAN%3ESQL%26nbsp%3BServer%26nbsp%3BManagement%26nbsp%3BStudio%26nbsp%3B%22Import%26nbsp%3Bdata-Tier%26nbsp%3Bapplication%22%26nbsp%3Boption%26nbsp%3Bfails%26nbsp%3Bafter%26nbsp%3Bsome%26nbsp%3Btime%26nbsp%3Bwith%26nbsp%3Ban%26nbsp%3Berror%20indicating%26nbsp%3B%22not%20enough%26nbsp%3Bmemory%22%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%3CSPAN%3EIf%26nbsp%3Bthe%26nbsp%3Bnumber%26nbsp%3Bof%26nbsp%3Btables%2C%26nbsp%3Bindexes%26nbsp%3Band%26nbsp%3Bviews%26nbsp%3Bis%26nbsp%3Blarge%2C%26nbsp%3Bimport%26nbsp%3Bmodel%26nbsp%3Bpreparation%26nbsp%3Bneeds%26nbsp%3Ba%26nbsp%3Blot%26nbsp%3Bof%26nbsp%3Bmemory%26nbsp%3Band%26nbsp%3Btime.%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3EIn%20this%20case%20the%20solution%20is%20use%26nbsp%3BSqlPackage.exe%26nbsp%3Bwith%20%22%2Fp%3AStorage%3DFile%22%26nbsp%3Boption%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%3CSPAN%3E%2Fp%3A%26nbsp%3BStorage%3D(%3C%2FSPAN%3E%3CSPAN%3E%7BFile%7CMemory%7D%3C%2FSPAN%3E%3CSPAN%3E)%26nbsp%3BSpecifies%26nbsp%3Bhow%26nbsp%3Belements%26nbsp%3Bare%26nbsp%3Bstored%26nbsp%3Bwhen%26nbsp%3Bbuilding%26nbsp%3Bthe%26nbsp%3Bdatabase%26nbsp%3Bmodel.%26nbsp%3BFor%26nbsp%3Bperformance%26nbsp%3Breasons%26nbsp%3Bthe%26nbsp%3Bdefault%26nbsp%3Bis%26nbsp%3BInMemory.%26nbsp%3BFor%26nbsp%3Blarge%26nbsp%3Bdatabases%2C%26nbsp%3BFile%26nbsp%3Bbacked%26nbsp%3Bstorage%26nbsp%3Bis%26nbsp%3Brequired.%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CP%3E%3CBR%20%2F%3E%3CU%3ESyntax%3C%2FU%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%3CSPAN%3Esqlpackage.exe%26nbsp%3B%2FAction%3AExport%26nbsp%3B%2Fssn%3Atcp%3A%3C%2FSPAN%3E%3CSPAN%3E%3CSERVERNAME%3E%3C%2FSERVERNAME%3E%3C%2FSPAN%3E%3CSPAN%3E.database.windows.net%2C1433%26nbsp%3B%2Fsdn%3A%3C%2FSPAN%3E%3CSPAN%3E%3CSOURCE%3E%3C%2FSOURCE%3E%3C%2FSPAN%3E%3CSPAN%3E-%3C%2FSPAN%3E%3CSPAN%3Edatabase%3C%2FSPAN%3E%3CSPAN%3E-%3C%2FSPAN%3E%3CSPAN%3Ename%3C%2FSPAN%3E%3CSPAN%3E%26gt%3B%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%26nbsp%3B%2Fsu%3A%3C%2FSPAN%3E%3CSPAN%3E%3CUSERNAME%3E%3C%2FUSERNAME%3E%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%2Fsp%3A%3C%2FSPAN%3E%3CSPAN%3E%3CPASSWORD%3E%3C%2FPASSWORD%3E%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%2Ftf%3A.%5Cexport.bacpac%26nbsp%3B%2Fp%3AStorage%3DFile%26nbsp%3B%2Fd%3Atrue%26nbsp%3B%2Fdf%3A.%5Coutputlog.log%26nbsp%3B%2Fp%3ALongRunningCommandTimeout%3D0%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%3CSPAN%3ESqlPackage.exe%26nbsp%3B%2FAction%3AImport%26nbsp%3B%2Fsf%3A.%5Cbacpacfile.bacpac%26nbsp%3B%2Ftsn%3A%3C%2FSPAN%3E%3CSPAN%3E%3CSERVERNAME%3E%3C%2FSERVERNAME%3E%3C%2FSPAN%3E%3CSPAN%3E.database.windows.net%26nbsp%3B%2Ftdn%3A%3C%2FSPAN%3E%3CSPAN%3E%3CDATABASE%3E%3C%2FDATABASE%3E%3C%2FSPAN%3E%3CSPAN%3E-%3C%2FSPAN%3E%3CSPAN%3Ename%3C%2FSPAN%3E%3CSPAN%3E%26gt%3B%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%2Fp%3AStorage%3DFile%26nbsp%3B%2Ftu%3A%3C%2FSPAN%3E%3CSPAN%3E%3CUSERNAME%3E%3C%2FUSERNAME%3E%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%2Ftp%3A**********%26nbsp%3B%2Fd%3Atrue%26nbsp%3B%2Fdf%3A.%5Coutputlog.log%26nbsp%3B%2Fp%3ALongRunningCommandTimeout%3D0%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%3CSPAN%3ENote%26nbsp%3Bthat%26nbsp%3Bsome%26nbsp%3Bimport%26nbsp%3Bsteps%26nbsp%3Bwill%26nbsp%3Btake%26nbsp%3Bsome%26nbsp%3Btime%26nbsp%3B(hours)%26nbsp%3Band%26nbsp%3Byou%26nbsp%3Bcould%26nbsp%3Bthink%26nbsp%3Bthat%26nbsp%3Bprocess%26nbsp%3Bis%26nbsp%3Bhang.%26nbsp%3BYou%26nbsp%3Bcan%26nbsp%3Buse%26nbsp%3BWindows%26nbsp%3B%22Resource%26nbsp%3BMonitor%22%26nbsp%3Band%26nbsp%3Bmonitor%26nbsp%3BSqlpackage.exe%26nbsp%3Bdisk%26nbsp%3Bactivity.%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3ENote%3A%20This%20solution%26nbsp%3Balso%20applies%26nbsp%3Bto%20export%20operations%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-support-blog%2Fusing-sqlpackage-to-import-or-export-sql-server-and-azure-sql-db%2Fba-p%2F368861%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-support-blog%2Fusing-sqlpackage-to-import-or-export-sql-server-and-azure-sql-db%2Fba-p%2F368861%3C%2FA%3E%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3ERegards%2C%20Paloma.-%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2451229%22%20slang%3D%22en-US%22%3E%3CP%3EImport%20BACPAC%20fails%20if%20database%20schema%20is%20large%3C%2FP%3E%3C%2FLINGO-TEASER%3E
Co-Authors
Version history
Last update:
‎Jun 15 2021 02:53 PM
Updated by: