“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!
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”
yes, you need partner license or dual right license.
in this case i used the partner license.
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.
yes, you are right!
the same thing applies to On-premise if I encrypt the data and do not provide the certificate, try.
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!
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
I am having this same issue and stuck at this step
Someone managed to solve Collation problem?
Thanks in advance.
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
Ho You handled the Collation error while doing Sync-NavTenant? I am stuck in that point.
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.
Did you ever find a solution?
Hy Snehanshu Mandal,
have you solved Collation problem? If yes, can you give same suggestions?
Thanks.