DMA DacPac files are not created

Question

Why dacpac files are not created?

I have 3 new SQL 2019 Enterprise instances on each Windows Server 2019 Standard
and 3 old SQL 2016 Enterprise instances on each Windows Server 2012 Standard.

Runing DMA from a new machine with DB Engine Accessment mode from SQL 2016 to SQL 2019 was successfull.

But the other two have failed.

I found that dacpac files were not created in the two servers %LocalAppData%\DataMigrationAssistant\DacFxAnalyzer-xxxyyyzzz .

Both old and new servers are on premise.

Tried both Domain Account and SQL Server Account, sa when connecting from new to old server when running DMA. Both account has full access to old and new servers.

No firewalls are set on both old and new servers.

Opened the DMA with "Run as administrator"

Below is the part of DMA Log.

Dma.exe Information: 0 : Extracting Dacpac Started: ActivityId = '9f6ed75d-6147-45a0-9d7c-ce87b2cfcaa0', DatabaseId = '77d8fd5a-e3ca-4058-bb8b-05cf3845abf1', AdvisorType = 'DacFxExport'.
ScenarioCommonTraceSource Verbose: 204 : 9696 : 63 : 01/31/2023 15:47:00 : OnDacProgressChanged(200) : Operation id 1, Status Pending, Message Validating schema
ScenarioCommonTraceSource Verbose: 204 : 9696 : 63 : 01/31/2023 15:47:00 : OnDacProgressChanged(200) : Operation id 1, Status Pending, Message Extracting schema from database
ScenarioCommonTraceSource Verbose: 204 : 9696 : 63 : 01/31/2023 15:47:00 : OnDacProgressChanged(200) : Operation id 1, Status Pending, Message Resolving references in schema model
ScenarioCommonTraceSource Verbose: 204 : 9696 : 62 : 01/31/2023 15:47:00 : OnDacProgressChanged(200) : Operation id 1, Status Pending, Message Validating schema model for data package
ScenarioCommonTraceSource Verbose: 204 : 9696 : 14 : 01/31/2023 15:47:00 : OnDacProgressChanged(200) : Operation id 1, Status Pending, Message Extracting schema
ScenarioCommonTraceSource Verbose: 204 : 9696 : 15 : 01/31/2023 15:47:00 : OnDacProgressChanged(200) : Operation id 1, Status Running, Message Extracting schema
ScenarioCommonTraceSource Verbose: 203 : 9696 : 19 : 01/31/2023 15:47:00 : OnDacMessage(194) : Dac Message Message SQL73097: Extracting schema (Start)
ScenarioCommonTraceSource Verbose: 204 : 9696 : 68 : 01/31/2023 15:47:00 : OnDacProgressChanged(200) : Operation id 1, Status Running, Message Extracting schema from database
ScenarioCommonTraceSource Verbose: 204 : 9696 : 66 : 01/31/2023 15:47:00 : OnDacProgressChanged(200) : Operation id 1, Status Faulted, Message Extracting schema from database
ScenarioCommonTraceSource Verbose: 204 : 9696 : 3 : 01/31/2023 15:47:00 : OnDacProgressChanged(200) : Operation id 1, Status Faulted, Message Resolving references in schema model
ScenarioCommonTraceSource Verbose: 204 : 9696 : 3 : 01/31/2023 15:47:00 : OnDacProgressChanged(200) : Operation id 1, Status Faulted, Message Extracting schema
ScenarioCommonTraceSource Verbose: 203 : 9696 : 3 : 01/31/2023 15:47:00 : OnDacMessage(194) : Dac Message Message SQL73100: Extracting schema (Failed)
ScenarioCommonTraceSource Verbose: 204 : 9696 : 3 : 01/31/2023 15:47:00 : OnDacProgressChanged(200) : Operation id 1, Status Faulted, Message Validating schema model for data package
Dma.exe Information: 0 : Unhandled exception reported, ErrorId '11733302271495205336', ExceptionName 'Win32Exception', Error '0x80004005', StackFrame '.', WatsonData '0x80090325', OuterExceptionName 'DatabaseAssessmentFailedException', OuterErrorCode '0x86020001', OuterCallSite '.', OuterWatsonData 'NotSpecified'
Dma.exe Information: 0 : 'Exception type: Microsoft.SqlServer.Advisor.Common.Contracts.DatabaseAssessmentFailedException
    Message: 
        The assessment of database 'myDB' on server 'oldSasServerName(NOT FQDN)' failed.
    HResult : 0x86020001
        FacilityCode : 1538 (602)
        ErrorCode : 1 (0001)
    Data: 
        DatabaseName = myDB
        ServerName = oldSasServerName(NOT FQDN)
        DpesErrorReportedId = 11733302271495205336
    Inner exception type: Microsoft.SqlServer.Advisor.Common.Contracts.DacpacExtractionFailedException
        Message: 
            Failed to extract dacpac for databaseId '77d8fd5a-e3ca-4058-bb8b-05cf3845abf1' and Advisor 'DacFxExport' at location 'C:\Users\myUserName\AppData\Local\DataMigrationAssistant\DacFxAnalyzer-332f94801180448fbf-myDB\myDB.dacpac'.

Answer

From the error, it appears it wasn't able to extract the dacpac. There might be some syntax issues due to object names or incompatible commands between the source and destination due to dbcompat level. You can isolate by trying to take a dacpac and exporting manually so you can identify which.

Answer

Hi mslee,

Dma.exe Information: 0 : Unhandled exception reported, ErrorId '11733302271495205336', ExceptionName 'Win32Exception', Error '0x80004005', StackFrame '.', WatsonData '0x80090325', OuterExceptionName 'DatabaseAssessmentFailedException', OuterErrorCode '0x86020001', OuterCallSite '.', OuterWatsonData 'NotSpecified'

Please refer to this MS document: Overview of Data Migration Assistant

You have to be a member of the SQL Server sysadmin role.

Best regards,

Seeya

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Share via

DMA DacPac files are not created

2 answers

Your answer