How to restore a Dynamics AX Cloud database to a local/one box DB

We have several AX7 environments (officially named “The New Dynamics AX”), in production.

If you want to learn more about this story :
The New Dynamics AX at Emile Egger
…or directly to the video :
Youtube video about AX at Emile Egger

At some point, we wanted to have the production database available on a single-box environment for development and testing. This is how you can do it.

Step 1 : Backup the database (skip if you have already a bacpac)

Create a storage account

Connect to https://portal.azure.com/, and go to “storage” (it works just as fine with the old Azure portal).

Then, add an account (unless you already have a suitable storage account, of course).

01_azure-storage02_create-accnt
Give it a name (it has to be a unique subdomain, but the name itself is not relevant : try until you find something available).
Choose the replication you want : if like me you just want a “temporary” storage, choose the weakest redundancy possible, in order to reduce the cost (I chose Locally Redundant). You can create a new resource group or just use an existing group, again, this is not a big deal : it is just a way to group your resources.

The location is the most important setting : you don’t want to transfer large amount of data across the globe just for database backup. Think about the rainforest and about your wallet! In my case, since my database server is located in “West Europe”, I chose a storage in the same location.

03_create-accnt

After a few minutes, your new account should appear in the list :

04_create-accnt

Create a container in the storage account

You can now click on the storage account, and create a container :

05_create-container

Leave “Private” in the “New container” dialog (as there is no reason to grant access to the world to your database backup).

Start the backup process

This step is very straightforward, but you need the SQL password. You can find this password on LCS, on your Environment page.

05_1_lcs

06_lcs_pwd

Now that you have your password, just go to the Azure SQL database, and choose “Export”. Give the subscription and select the storage account.

07_backup

Click “ok”, and go hunt some Pokemons, as the backup is quite long (the system needs to create and compress a database backup of a few GBs).

You can check the progress of the backup under SQL Servers > Import/Export history. Don’t worry if the process is stuck at 0% for a long time (it might be at 0% for an hour).

08_backup-progress

Once the backup is complete, you can proceed to Step 2. Feel free to comment on the blog to let us know what pokemons you caught during the backup process : I personally caught my very first one, a Clefairy of 4.6 Kg 🙂

Step 2 : Restore the database

There are many ways to restore an Azure database to another server, but the other solutions I tried failed. In particular, trying to restore the backup “in-band” with SQL Server Management Studio (SSMS) and the storage access keys took more than 10 hours, just to fail at the end…

Download the bacpac

Download the bacpac file directly on the machine where you want to restore your backup. As the bacpac is compressed, it should not be too long. My database was 3+ GB, and compressed with all the data, it is only 50 MB.

10_blob-download

Note that the downloaded file has extension .zip : just rename it to .bacpac.

Get SqlPackage.exe

In order to restore your bacpac, you will need the latest version of SqlPackage.exe : to do so, you can simply install the latest version of SSMS :

MSDN SQL Server Management Studio download

If you run in trouble with an exception like :
“Internal Error. The internal target platform type SqlAzureV12DatabaseSchemaProvider does not support schema file version ‘3.3’”
(that’s what happened to me on the one-box AX7 DEV environment), then just install the latest version of SSMS.

If you try to restore from the SSMS user interface (Import Data-Tier Application) you might end up with an out of memory error…

The safest and quickest way I found to restore the database was just to use SqlPackage.exe in command line :
PS C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin> .\SqlPackage.exe /a:Import /sf:"C:\Users\axlocaladmin\Downloads\axdb_XXX-2016-8-11-15-11.bacpac" /tdn:axprod20160811 /tsn:TEST-1
/tdn is the name of the database to which you want to restore the file (pick any name you want)
/tsn is the SQL server name

…again, this process is long (but very fast compared to the other methods I tried).
Another way to solve the out of memory problem might be to right-click on the server in SSMS > Properties > Memory… but SqlPackage is quite nice.

Finally, you can check the content of your database against the source database, for instance by using this query and comparing with the source db :

SELECT TOP 20
t.[name] AS TableName,
p.[rows] AS NbRows
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.object_id = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE
t.[name] NOT LIKE 'dt%' AND
i.[object_id] > 255 AND
i.[index_id] <= 1
GROUP BY
t.[name], i.[object_id], i.[index_id], i.[name], p.[rows] ORDER BY
p.[rows] DESC

db

7 thoughts on “How to restore a Dynamics AX Cloud database to a local/one box DB”

  1. Hi Raphael. Great post!
    Thanks for share your experience and knowledge.

    Once we restore the DB and point to it from the AOS web.config, are there some other task to do in order to prepare the data for the DEV/TEST enviroment?

    Thanks in advance.

    1. Dear Daniel,

      Yes, there are further steps, but that’s feasible.
      Let me check with MS if they let me share this information, I’ll come back with the reply (and let’s hope the information) in a few days.

      Have a nice day!

  2. Dear Raphael, thanks for the quick response.

    Right now, I’m trying to import a DACPAC from my TEST environment (UAT) into my DEV env.
    I’m using the sqlpackage.exe approach and I’m receiving some errors related to Contained database feature.
    Shortly, the BACPAC deployment creates the DB with Change Tracking = TRUE, and then tries to modified the Containment Type to PARTIAL, which is resulting in throwing an error:
    “Replication, Change Data Capture and Change Tracking are not supported in contained databases. The database ‘TEST’ cannot be altered to a contained database, since it has one of these options turned on”
    The executed script:
    IF EXISTS (SELECT 1
    FROM [master].[dbo].[sysdatabases]
    WHERE [name] = N’$(DatabaseName)’)
    BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET CONTAINMENT = PARTIAL
    WITH ROLLBACK IMMEDIATE;
    END

    How could I overcome this?

    Let’s hope you can get some feedback and authorization from the MS team. Good luck!
    Hava a nice weekend!

  3. Hi Raphael!

    Finally I have been able to import the BACPAC file into my DEV environment.
    Before importing, I’ve had to modify the file and do some hacking to leave it allowable for SQL Server. Once imported, a few parameter tables needed some tunning in order to AX starts succesfully

    Anyway, it would be nice if you could share some information related to this procedure.

    Thanks!

  4. Hi Daniel,

    Good job!

    Unfortunately, no news yet from MS, but they are discussing it.

    Another tip : the machine may be very slow, once you have restored the database. You have to tweak the enabled services. Once you disable those you don’t need, it is fast as lightning.

    Have a nice day!

  5. Thanks for the update!

    I have already read these posts but I forgot to mention them to you. Great articles !

    You mentioned before: “..the machine may be very slow, once you have restored the database. You have to tweak the enabled services. Once you disable those you don’t need, it is fast as lightning..”

    What services do you mean ?

    Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *