Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / backpac file import er...
Finance | Project Operations, Human Resources, ...
Suggested answer

backpac file import error in Dev environment - Error importing database

(0) ShareShare
ReportReport
Posted on by Community member
Hi Peers,
 
While importing the bacpac file (database) into Dev environment, prompting errror.
 
1) I've downloaded sql package file and extracted
2) downloaded backpac file and started running below script in command prompt
 
SqlPackage.exe /a:import /sf:/C://Users//Admin//Bakpac files//gldbackup.bacpac/ /tsn:localhost /tdn:AxDB_ENV_24112023_02 /p:CommandTimeout=0 /TargetTrustServerCertificate:True
 
3. I tried above with both stopping the services and without stopping the services as well (Batch, management reporter,life cycle diganostic, world wide web..etc), always failing in 15 minutes
 
Error:
Error importing database:Could not import package.
Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 207, Level 16, State 1, Procedure AutotuneEvictPlansFromCache, Line 24 Invalid column name 'plan_forcing_type_desc'.
Error SQL72045: Script execution error.  The executed script:
CREATE PROCEDURE [dbo].[AutotuneEvictPlansFromCache]
@QUERY_ID BIGINT, @EVICTED_PLANS NVARCHAR (MAX) OUTPUT, @CLEARED_QUERY_HINT NVARCHAR (MAX) OUTPUT
 
Find below screenshot for error reference:
 
////
  • Suggested answer
    krishna.rao@dax Profile Picture
    krishna.rao@dax 2,953 on at
    backpac file import error in Dev environment - Error importing database
    Hi Team,
     
    We contacted Microsoft, they said it's bug in new version. below are the steps to do 
     
    1. Connect to SQL database from the environment you are taking the backup , source environment

    2. Find the Stored Procedure in the DB tree -> AXDB -> Programmability -> Stored Procedures – search for AutotuneEvictPlansFromCache

    3. Right click on it -> “Script Stored Procedure as” -> CREATE TO -> New Query Editor Window; Then backup the script to a file (using CTRL+S).

    4. Open a new query and run this on AXDB: DROP PROCEDURE AutotuneEvictPlansFromCache

    5. Take a new database backup and restore it in DEV environment. (here follow normal process.. bacpac file download in destination vm and importing through scripts..etc.)

    6. Once the export is complete, run the script saved in step 3 in source environment to restore the dropped procedure.
  • Community member Profile Picture
    Community member 5 on at
    backpac file import error in Dev environment - Error importing database
    Hi all, I also had to edit the model.xml file as per what Judy suggested in order to get the restore to work as well. What is weird that previously I did not need to do this to get it to work. 
  • Community member Profile Picture
    Community member 5 on at
    backpac file import error in Dev environment - Error importing database
    Thank you, Judy, for your response. Editing the model.xml file did the trick!
  • Community member Profile Picture
    Community member 5 on at
    backpac file import error in Dev environment - Error importing database
    Hi all, I am running into the same issues as well. The last user suggestion of ensuring the SQL server versions for the backup is the same may not be possible since the bacpac was created in LCS and there doesn't seem to be controls to set the SQL version for the backup. 
  • Community member Profile Picture
    Community member 2 on at
    backpac file import error in Dev environment - Error importing database
    Here are a few steps you can take to troubleshoot and resolve the issue:
    Database Compatibility Level: Ensure that the target SQL Server instance has a compatible compatibility level with the bacpac file. Check the compatibility level of the source database and adjust the target if needed.
    SQL Server Version: Verify that the SQL Server version on your local machine matches or is compatible with the version used when creating the bacpac file. Sometimes, differences in SQL Server versions can cause import issues.
    Database Compatibility Level in bacpac: If you have control over the source database, you might consider adjusting the compatibility level before creating the bacpac file. This can be done using SQL Server Management Studio (SSMS).
    Script the Stored Procedure: If the issue persists, you can try scripting the stored procedure directly from the source database, excluding any invalid or unnecessary columns. Modify the script manually if needed and then try importing again.
    Upgrade SQL Server Management Studio (SSMS): Ensure you are using the latest version of SQL Server Management Studio. Newer versions might have better compatibility with certain SQL Server features.
    Check for Updates: Make sure that both SQL Server and SQL Server Data Tools (SSDT) are up to date with the latest service packs and updates.
    After making any adjustments, attempt the import process again and see if the issue is resolved. If the problem persists, you may need to dive deeper into the stored procedure code and database schema to identify and address the specific compatibility issue.
  • Suggested answer
    Judy Profile Picture
    Judy Microsoft Employee on at
    backpac file import error in Dev environment - Error importing database
    Hi,
     
    These are the workaround steps to update model.xml manually so that they can unblock yourself.
    1. unzip the bacpac file.
    2. open the unzipped folder and open the model.xml file in it.
    3. Search for the procedure(AutotuneEvictPlansFromCache) which is referencing "plan_forcing_type_desc" and remove the procedure and its related dependencies(if any) from model.xml
    4. run the below query to import the bacpac using the updated model.xml file
    5.  Please find below the sample import query to do it:
      "C:\path\to\sqlpackage.exe" /a:import /tcs:"Data Source=dataSource;Initial Catalog=dbName;Pooling=False;MultipleActiveResultSets=False;Integrated Security=True;Trustservercertificate=true" /sf:"C:\path\to\LatestProdBackup.bacpac" /ModelFilePath:"C:\path\to\attached\modelFile\model.xml".
    Hope it helps.
     
     
    Best Regards,
    Judy
  • Community member Profile Picture
    Community member 5 on at
    backpac file import error in Dev environment - Error importing database
    I'm having the same exact error, using SQLPackage version 162.1.167.1, and after clearing the cache. The target database version is 13.0.5893.48. I suspect that has something to do with it.
  • Suggested answer
    Judy Profile Picture
    Judy Microsoft Employee on at
    backpac file import error in Dev environment - Error importing database
    Hi Sir,
     
    Please try the newest version of sqlpackage (162.1.167): Download and install SqlPackage - SQL Server | Microsoft Learn to see if it can help. 
    Since it has included numerous bug fixes in this release.
     
    Best regards,
    Judy
  • Suggested answer
    Komi Siabi Profile Picture
    Komi Siabi 12,729 Most Valuable Professional on at
    backpac file import error in Dev environment - Error importing database
    Hello,
     
    You should :
     
    1. Clear the cache in SQL studio.
    2. Use a more simplified DB name as AXDB01 instead of AxDB_ENV_24112023_02.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,642 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,371 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans