“How-to” use the restored Business Central SaaS database on On-premise environment – Part II

“How-to” use the restored Business Central SaaS database on On-premise environment – Part II

Some have written to me about the possibility of using the Business Central SaaS database restored on SQL Server in a Business Central On-premise environment.

In my old post I actually illustrated how to back up the SaaS db so you can save it on Azure Services and download it for restoration on the local SQL Server. In fact, the aim is precisely to be able to download a copy of the SaaS DB, not to be able to use it on a local BC On-premise environment or in Azure subscription.

Actually, however, it should be possible to use it, I found some workarounds to do it. I know it is not an excellent recovery, as it is not complete (there are no installed apps and updated system apps) but it can be useful to be able to immediately navigate the data through the Business Central Web Client).

RESTORE of a SAAS DB on ON-PREMISE BC 15.2 – STEP BY STEP

#1 – PRE-REQUIREMENTS

Download the backup copy of the SaaS database .
Restore the downloaded database in “bacpac” format on your local SQL Server.

Follow this my old post to achieve these two goals: https://robertostefanettinavblog.com/2019/11/07/business-central-saas-exporting-database-backup-and-restore-it-on-your-sql-server/

Download the on-premise version equivalent to that of the downloaded bacpac (in this case v15.2)

VERY IMPORTANT: as the use in both single and multi-tenant requires the same platform version!

Example

Version > BACKUP PRODUCTION DB > Production v15.2.39060.0 ITA

Business Central, Production, Active, IT 15.2.39060.0

#2 – DOWNLOAD DVD RELEASE FOR BUSINESS CENTRAL ON-PREMISE

Download the Business Central On-premise DVD

In this case, you need this release: “Update 15.2 for Microsoft Dynamics 365 Business Central 2019 Release Wave 2 (Application Build 15.2.39040, Platform Build 15.2.38951)”

Direct download link https://support.microsoft.com/en-us/help/4533389/update-15-2-for-microsoft-dynamics-365-business-central-2019-release-w

RESTORE THE SAAS DATABASE

  • Complete

INSTALL BC ONPREMISE WITH DEMO DATABASE

In this case: Demo Database BC (15-0) ITA

#3 – SCENARIO (SAAS to ONPREMISE)

Scenario

It is possible to restore DB SaaS to DB Onpremise in two ways

  • Single tenant
  • Multi-tenant

The easiest and fastest is the single tenant

Limitations

it is possible to load the data present in the SaaS db using the base and the system app of version 15 without restoring the installed apps or perform the whole process to install the correct base and system app by downloading the exact build of the Cloud installation (you need to search for that build as a CD upgrade or via docker).

It is necessary to manually change some parameters on SQL Server (e.g. Db Collation, version of the platform and of the installed system apps otherwise you cannot use)

In the backup of the SaaS db there are also the APPs installed and published, since it is not possible to unmount them before the backup: either I delete them from Sql or I install them first in the app base of the Onpremise db, otherwise the system reports errors.

#4 – POWERSHELL SCRIPTS – “HOW-TO”

With this script, it is possible to configure the Business Central Server BC150 to connect it to the Restored SaaS db, in this case for single-tenant.

POWERSHELL SCRIPT EXAMPLE

Source Powershell Script – Single Tenant link

https://github.com/rstefanetti/Powershell/blob/master/PowerShell%20Onpremise%20Single%20Tenant.ps1

MANUL CORRECTIONS

CHANGE THE SQL SERVER COLLACTION ON Demo Database BC like the SaaS DB

CHANGE SOME PARAMETERS

Table $ndo$tenantproperty, $ndo$tenantdatabaseversion, $ndo$tenantdatabaseproperty

CHECK INSTALLED APPS \ NAV APP (BC DEMO e SAAS DB)

  • Change Versions for Microsoft Apps, Remove the custom installed APPS

DELETE APPS ON BCDEMO

OR DELETE USING SQL SCRIPTS

CREATE AND ASSIGN USER FOR ADMIN LOGIN

DELETE ROLES FROM ACCESS CONTROL (SAAS DB)

TIPICAL ERRORS

PUBLISHED APPS

  • Remove Published Apps

SQL COLLACTION

  • Change SQL Collaction

#5 – BINGO! YOUR BC ONPREMISE IS READY!

Restart BC DEMO (BC150) SERVER

Now your instance is connected to restored SaaS DB! ….after you can publish and install your extensions.

YOU ARE WELCOME!

Thanks to Duilio Tacconi (Microsoft Escalation Engineer) for support and advices! always available!

13 thoughts on ““How-to” use the restored Business Central SaaS database on On-premise environment – Part II

  • 6 March 2020 at 5:03 AM
    Permalink

    do you need to use client license to be able to run this on prem ? I try to restore my client backup to on-prem using partner license and I got an error when trying to connect using web client. The error is “You don’t have a paid license to run Production companies”

    Reply
    • 6 March 2020 at 7:43 AM
      Permalink

      yes, you need partner license or dual right license.
      in this case i used the partner license.

      Reply
  • 6 March 2020 at 9:17 AM
    Permalink

    Hi, What about encrypted data?
    You can’t export a certificate from the cloud (Data Encryption Management page).
    So encrypted data won’t be readable/usable, right?

    Which also means that whatever we put to into isolated storage with encryption in our app, is safe even though with the restored backup you can browse [Isolated Storage] table from SQL.

    Reply
    • 6 March 2020 at 9:29 AM
      Permalink

      yes, you are right!
      the same thing applies to On-premise if I encrypt the data and do not provide the certificate, try.

      Reply
  • 6 March 2020 at 9:37 AM
    Permalink

    Hi Guys …
    this post generated incredible traffic … I don’t understand why.

    SOME NOTES
    the example was made to be able to use a database locally (read data from BC and perform tests on standard features) as indicated on the Microsoft website (“you can use restored database also on On-premise …”)

    SQL Script insted of Powershell Script
    I know that it is also possible to do it from SQL as someone pointed out to me (I did it last year but I don’t provide the script), this blog for Business Central.

    Cloud Certificate
    If encryption has been activated, obviously we do not have the certificate installed on the Cloud, your apps are safe!

    Reply
  • 16 March 2020 at 8:21 AM
    Permalink

    Hi, Thanks for your technical article. I am trying to restore the database of SaaS to On-Pre.
    I have a problem soon. About the step “CHANGE THE SQL SERVER COLLACTION ON Demo Database BC like the SaaS DB”.
    I can’t change COLLACTION, As long as I change it, I will get the following error message.
    If I run export directly, it will remind me that the two database versions are inconsistent.
    Did I miss a key step?
    Very thanks.

    Change COLLACTION Error:
    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ——————————

    The object ‘CRONUS International Ltd_$Cost Entry$437dbf0e-84ff-417a-965d-ed2bb9650972$VSIFT$Key9’ is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
    The object ‘CRONUS International Ltd_$Issued Reminder Line$437dbf0e-84ff-417a-965d-ed2bb9650972$VSIFT$Key2’ is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
    The object ‘CRONUS International Ltd_$Action Message Entry$437dbf0e-84ff-417a-965d-ed2bb9650972$VSIFT$Key3’ is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
    The object ‘CRONUS International Ltd_$Issued Reminder Line$437dbf0e-84ff-417a-965d-ed2bb9650972$VSIFT$Key3’ is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
    The object ‘CRONUS International Ltd_$Issued Reminder Line$437dbf0e-84ff-417a-965d-ed2bb9650972$VSIFT$Key4’ is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
    The object ‘CRONUS International Ltd_$Requisition Line$437dbf0e-84ff-417a-965d-ed2bb9650972$VSIFT$Key8’ is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

    Export Error:
    Export-NAVApplication : The source and destination databases have different collations. Either change the collation of destination dat
    abase manually to match the source database or, instead of using Export-NAVApplication, use the Export-NAVData cmdlet on the source da
    tabase and Import-NAVData cmdlet on the destination database (this will change the collation of the data to match the destination data
    base automatically).
    Source database (Demo Database BC (153)): Latin1_General_100_CS_AS
    Destination database (SaaSProduction): Japanese_XJIS_100_CI_AS
    At line:1 char:1
    + Export-NAVApplication -DatabaseServer Test -DatabaseInstance BCDEMO – …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (0:Int32) [Export-NAVApplication], NavCommandException
    + FullyQualifiedErrorId : Test,Microsoft.Dynamics.Nav.Management.Cmdlets.ExportNavApplication

    Reply
  • 7 July 2020 at 3:10 PM
    Permalink

    I’m using partner license but I get this message: Your trial period expires in 9 days. Ready to subscribe, or do you need more time?

    Any idea about how to solve this problem?

    Thanks in advance

    Reply
  • 26 March 2021 at 11:03 AM
    Permalink

    i imported the .bacpac file on SQL Server with option ‘Import data-tier application’, but when I try to start the instance of my Businees Central On-Premise, event viewer telling me that the System table $ndo$dbproperty is missing.

    Reply
    • 31 August 2022 at 5:37 PM
      Permalink

      Did you ever find a solution?

      Reply
  • 30 September 2021 at 1:34 PM
    Permalink

    Hy Snehanshu Mandal,
    have you solved Collation problem? If yes, can you give same suggestions?
    Thanks.

    Reply

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.