The other day one of my colleagues needed a database copied from PROD to a DEV OneBox. Unfortunately there is no direct route from PROD to Onebox, you need to make a refresh to a Tier-2 environment and then move the database from the Tier-2 environment to LCS and then restore it to the OneBox environment. The issue I had was that I need to keep the data in the Tier-2 environment.
I used LCS to make an export to LCS, performed the entire process from above and it was finally time to restore the export… did not work… The issue I was getting was an error trying to create a trigger referring to a missing column. When I looked closer at this I found that the column was missing had been removed a long time ago and (in AX/D365 removed columns gets the prefix DEL_ and this column had that). Apparently the column had been removed before the export was done and for some reason the export was not consistent.
There are a couple of different solution to this problem…
In my case I opened up the bacpac file (it is apparently a zip file). Inside is all the data and the schema for the database. I opened the model.xml file and search for the T-SQL statement that created the trigger, I changed the statement to point to the DEL_ table, saved the file and put it back into the zip and renamed it back to bacpac.
When I tried to import it I got an error that said “mismatching checksum”. In the file called origin.xml there is a line containing the checksum for model.xml:
<Checksum Uri=”/model.xml”>8E0D68CEA76ACDB4DD4EE9853065E0E10D9C426F0CC880D18ABC19AAC64624C9</Checksum>
This prevents someone tampering with the model file. To generate a ned checksum for the model file, run the following PowerShell script and point to the model.xml file. Past the HASH in origin.xml
$modelXmlPath = Read-Host "model.xml file path" $hasher = [System.Security.Cryptography.HashAlgorithm]::Create("System.Security.Cryptography.SHA256CryptoServiceProvider") $fileStream = new-object System.IO.FileStream ` -ArgumentList @($modelXmlPath, [System.IO.FileMode]::Open) $hash = $hasher.ComputeHash($fileStream) $hashString = "" Foreach ($b in $hash) { $hashString += $b.ToString("X2") } $fileStream.Close() $hashString
Put both Origin.xml and model.xml in the zipfil and change the extension to bacpac. Import it again.
The final takeaway: DO NOT RELY ON BACPAC EXPORTS for backup!!! Or at least test them in some way…
Note added after post:
A safer way to do this is probably (as long as we still have RDP access to t-2 environment):
Creating a new database as a copy of the original (since you are going to mess with it) by running this SQL query:
CREATE DATABASE MyNewCopy AS COPY OF axdb_mySourceDatabaseToCopy
Since this operation runs asynchronously in the background you will have to keep an eye on it before continuing:
SELECT * FROM sys.dm_database_copies
Leave a Reply