We have several AX7 environments (officially named “The New Dynamics AX”), in production.
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).
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.
After a few minutes, your new account should appear in the list :
Create a container in the storage account
You can now click on the storage account, and create a 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.
Now that you have your password, just go to the Azure SQL database, and choose “Export”. Give the subscription and select the storage account.
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).
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.
Note that the downloaded file has extension .zip : just rename it to .bacpac.
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 :
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
sys.indexes i ON t.object_id = i.object_id
sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
t.[name] NOT LIKE 'dt%' AND
i.[object_id] > 255 AND
i.[index_id] <= 1
t.[name], i.[object_id], i.[index_id], i.[name], p.[rows] ORDER BY