This is going to be a very short blog post, but hopefully still helpful for those of you in the same situation as me: How can I find out what apps are installed in an export from a Microsoft Dynamics 365 Business Central Online environment?

As Freddy Kristiansen in his blog and others have shared before, it is possible to use a BC Online environment export (this explains how to create one) and restore it in an onprem environment. In COSMO Alpaca this is fully automated, so fortunately I don’t have to deal with all the setup and scripting for this, but one challenge remains: How can I find out in advance, which apps in which versions I will need? I can let the process run until it fails to mount the tenant database because of missing apps, but I can get there easier and faster: If the export is restored to a database even without BC connected to it, I can use the following SQL commands to find out

1
2
SELECT Name, Publisher, [Version Major], [Version Minor], [Version Build], [Version Revision], [App ID], [Package ID], [Extension Type], [Published As], [Compatibility Major], [Compatibility Minor], [Compatibility Build], [Compatibility Revision] FROM [mydb].[dbo].[NAV App Installed App]
SELECT Name, Publisher, [Version Major], [Version Minor], [Version Build], [Version Revision], [App ID], [Package ID] FROM [mydb].[dbo].[NAV App Published App]

As you have probably guessed, the first one gives you the installed apps, and the second one the published apps. If you don’t happen to have a SQL Server readily available, you can easily just use a container:

1
docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=YourStrong!Passw0rd' --name 'sql1' -p 1401:1433 -d mcr.microsoft.com/mssql/server:2022-latest

Note that Microsoft only releases official SQL images for Linux (I am not kidding), but with e.g. Docker Desktop and the WSL backend that is not a problem even on a Windows machine. Once that is up and running, you can use e.g. the Azure Data Studio to connect and restore the database and execute these commands. If you automate the process, you can also use the following commands to run the above commands directly in the container:

1
2
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'YourStrong!Passw0rd' -Q 'SELECT Name, Publisher, [Version Major], [Version Minor], [Version Build], [Version Revision], [App ID], [Package ID], [Extension Type], [Published As], [Compatibility Major], [Compatibility Minor], [Compatibility Build], [Compatibility Revision] FROM [mydb].[dbo].[NAV App Installed App]'
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'YourStrong!Passw0rd' -Q 'SELECT Name, Publisher, [Version Major], [Version Minor], [Version Build], [Version Revision], [App ID], [Package ID] FROM [mydb].[dbo].[NAV App Published App]'

That’s what I wanted to share this time. As I wrote in the beginning, not much, but I hope it is still useful :)