Restore an RTC SQL developer database

Here you will find instructions on how to create a test environment and/or data backup of a Navision SQL Server environment via the SQL Server Manager.

Introduction

If you don't do it that often, you forget a step every now and then. This is annoying because it takes longer later on. That's why I've written down the steps for restoring an RTC/SQL customer database from Navsion 2013/Business Central (not a native database, that's just a joke anyway, not the native Navision under SQL) for test purposes / development purposes / as a development environment. I often do this as a virtual machine because I never have to worry about dependencies with other installations.

Extra tip: You can get Windows 7 license stickers in Ebay classifieds from time to time. I think they are perfectly legal. Definitely more "legal" han the "Windows 10 for an apple and an egg" offers you get on Amazon & eBay. With these Windows 7 license numbers, a Windows 10 Prof can be activated wonderfully (at least until 16.8.2021). That's more than enough for a development environment!

Don't go through the trouble of installing the Business Central Visual Studio development environment as Docker! Navision or Business Central is only explained as Docker in the Microsoft documentation because Microsoft is proud to be able to offer Docker. It is simply not necessary and, in my opinion, causes more difficulties for the C/Side client than it simplifies. With Business Central, Visual Studio Code and Extensions, it's a completely different story!

If you create a new VM (recommended: HyperV, already included free of charge with Windows 10 Pro) for this purpose, please note the minimum hard disk size of 15 GB (more if you have a larger database, of course, but not less) and allocate 4 processors and 4096 MB RAM, then the whole thing will be fun right from the start.

Here you will find detailed instructions on how to set up a suitable HyperV VM for Navision or Business Central
Please also remember to activate Dotnet 3.5 (contains 2.0) in the Windows features .
You activate .net 3.5 (and therefore also 2.0 if you want to virtualize an old Navision) here:

And use an admin user for the service, which you have previously given the right to run as a service via the group policy . You can do this here:

Now it's time to get started with the...

Installation instructions for Navision / Business Central

1. Install Navision from the correct source (correct version) as a demo, but do NOT install the demo database.
   Enter the currently active (administrator) user including password as the user. Via the Local Security Policy / Security Settings / Local Policy / Assign User Rights / Log on as Service, give this user the right to log on as a service (this is where 99% of all demo installations fail...).
   
2. For databases over 10 GB, update the SQL server installed in this way to the Developer Edition, which is also free but not limited.
   
3. Install the SQL Manager.
   
4. Restore the SQL Server data backup BAK with the SQL Manager (tip: delete any existing test clients before creating the database).
   
5. Connect to this restored database in the development environment: This should work now!
   
6. Enter the correct database in the Navision administration console installed at 2.
   
7. Use the script below to remove the users from the database.
   The old users have to go because a) you don't have the old SecurityIDs on the new server anyway and b) that's exactly why you can't access the interface: you don't have a valid login! Without a user, Navision & Business Central immediately logs you in as a superuser.
   
8. (Re)start the Navision service in the administration console.

Delete old users

After the restore, the Navision or Business Central database still contains the users... more precisely: the user IDs of the old domain (environment) of the backed up ERP system. The Windows users are referenced via the SID of the old domain. These SIDs are generally not available when the database is restored.

Since this is about restoring for test/development purposes, let's just take a hammer to the user list. Reason: If there is no Business Central or Navision user in the database, Navision does not take care of access control! User gone = free access to the database.

However, unlike usual, I am not going to give such detailed instructions here. If you don't know how to use this script, then - believe me - it's better not to use it!

USE Wiederhergestellte_Datenbank;
GO
delete from [dbo].[User]
delete from [dbo].[Access Control]
delete from [dbo].[User Property]
delete from [dbo].[Page Data Personalization]
delete from [dbo].[User Default Style Sheet]
delete from [dbo].[User Metadata]
delete from [dbo].[User Personalization]
/*delete from [dbo].[Profile]*/

I also had a wonderful error here! Page xxxxx is not a role center page. And it was simply not possible to start the application because it was aborted immediately. This error is then in the profiles in which a role center ID was entered with a (no longer) existing one... and was perhaps still defined as the default 🙂 In this case, delete the comment function /* at the top of the script, which then deletes all profiles. When Navision RTC / Business Central is started, all standard profiles(!) will be recreated from the definitions of the pages. This means that there will no longer be any invalid profiles.

The following script should also clean up the users under Navision up to 2009R2 under SQL. For Nav up to 2009R2, it is recommended to work with the FBK backup and to simply work with a local native database for development. Reason: After importing an FBK database, you can immediately (not after logging off!!!) go to the user control and adopt one of the users there. If the backup is from SQL, you will find numerous users without any password.

USE Wiederhergestellte_Datenbank;
GO
DELETE FROM dbo.[Member Of];
DELETE FROM dbo.[User];
DELETE FROM dbo.[Windows Access Control];
DELETE FROM dbo.[Windows Login];

From here on, everything should run normally.

Collected error messages during installation

SQL Server is not responding

This can only happen if the SQL server and the Navision server service are running on two different servers.
In this case, the reason is often an unopened network service or a firewall that is not configured or is configured incorrectly.

The named pipes must be enabled in the SQL server:

To be on the safe side, check whether the client protocols are activated:

If, for example, automatic data backups are to be carried out, the SQL Server Agent must be running. If this SQL Server is to be found in the network (Active Directory Services, ADS), the SQL Server Browser must also be running:

By the way, this is a good place to get an idea of why SQL actions (reading or creating data backups...) run at a different level of rights than the current user login: "Log in as" specifies the rights that the SQL server has.

The SQL Server can be accessed from outside via the standard ports TCP 1433, 4022, 135, 1434 & UDP 1434. These should therefore be enabled in the firewall for incoming data traffic. For "named instances", an additional port is added. This is set by default by the SQL server when the database server is started, so it is not known in advance. Only if there is no other database server/database service on this machine will port 1433 possibly also be used for this named instance.
If named instances are used, it is therefore recommended that C:\Program Files\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\Binn\Sqlservr.exe be allowed as an exception in the firewall rule. This only works with the Windows firewall, of course. With an external firewall, the SQL server must be switched to a static port in case of an emergency:

It is very easy to determine whether the Windows firewall is the problem: simply switch off the Windows firewall and check whether the SQL server can now be accessed. This test is easiest to perform up to BC14 using the C/Side Navision development environment, even if you want to use AL later.

Error message "Dependency service"

MicrosoftDynamicsNavServer$DynamicsNAV110′ failed to reach status 'Running' on this server due to the following error: 'The dependency service or dependency group could not be started'. Additional information may be in the event log of the server.

In most cases, a look at the event log is really helpful and should be the first thing you do. However, this does not help with dependencies (last tip).

There are (at least) 3 ways to correct it.

• In the Navision settings, check whether the correct database data has been entered. Also make sure that the order of the data is not logical: first the instance, then the database name, then the server name:
  

• In the Navision settings, try switching the service to the "Network Service" account and restarting it. If this is successful, try reactivating the original user:

The second solution: Please look at the service dependencies:

Please check in the services whether the services listed there are installed and started.
Its status cannot be queried via services , but also on the command line with sc query http.
A non-running HTTP can be controlled with net start http and net stop http .

MsQuic is harder because MsQuic only comes with a new Windows version on the computer. If necessary, you should try to solve the dependency with Set-Itemproperty -path ‚HKLM:\SYSTEM\CurrentControlSet\Services\MRXSMB‘ -Name ‚DependOnService‘ -value ‚rdbss‘ Restart-Computer , or
copy the MsQuic key from a running Navision service computer:
[Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\WinQuic]

Setup cannot find ocsetup.exe

But then you really have an old treasure in front of you, namely a 2009r2/2013.
Please make sure that the Windows feature .Net 3.5 (includes 2.0) is activated!

Setup aborts with -2068119551

The Navision or Business Central setup terminates with an error message. The log contains the termination error for the SQL Server -2068119551 and the text Attempted to perform an unauthorized operation.
In this case, simply install and configure the SQL Server manually BEFORE the next setup attempt. It can be found in the folder D:\Navision_Setup\2018 BusinessCentral\V11.0.27667 CU14\Prerequisite Components\Microsoft SQL Server as SQLEXPR_x64_ENU.EXE.

The path could not be created.

The event viewer then displays a message like this: ParentException: NavNCLDirectoryCreationException The path 'C:\ProgramData\Microsoft\Microsoft Dynamics NAV\140\Server\MicrosoftDynamicsNavServer$BNW\base\staticsource' for this service instance could not be created. Make sure that the access rights for the service account have been set up correctly.
Have you previously installed with a different user ID? Navision may simply be denied access to the existing directory.
Perform a complete uninstall.
Please then check whether these directories/files have been removed. If not, please delete them.

• C:\Users\xxxx\AppData\Roaming\*.zup (bitte alle User durchsuchen)
• C:\ProgramData\Microsoft\Microsoft Dynamics NAV
• C:\Program Files (x86)\Microsoft Dynamics*

Please then carry out a new setup with the correct user. Also remember to give this user the right to register as a service.

Navision / Business Central simply won't install

It can happen: Navision or Business Central will not install for the life of it, neither the client nor the administration interface (and thus also not the 2-tier Navision server).

One trick is to unplug the computer from the power supply just before the rollback to prevent the rollback. This way, we have the Navision / Business Central server including the administration interface on the computer.

At least one thing helped with the client: waiting. The next day, the client was running... without us having changed anything. Microsoft...

Important: NEVER use a pre-installed computer or a "used" Windows installation to install Navision / Business Central 1-Tier (MS-SQL-Server) or 2-Tier ("Navision / Business Central Server) on it. Never. Install the OS freshly out of the box on the computer, and then the Navision / Business Central setup routine.

But even then, there are some really tough nuts to crack, and you just can't get Navision to work. In this case, you can actually install the server service entirely by hand.

You will then receive an error message like this:

Server
Microsoft Dynamics 365 Business Central Server Components
Fatal error during installation.

The service ‚Microsoft Dynamics 365 Business Central Server [BC140]‘ (MicrosoftDynamicsNavServer$BC140) failed to start. This can be caused by one of several issues. For example, the password for the account that runs the service may be incorrect, or there could be a conflict in port sharing for the port settings for the service, or you have specified that the port for the server should be opened in the Windows firewall even though the Windows firewall is not enabled on the computer. For more information, see the event viewer.

If you have followed my instructions here, the problem with the wrong user, missing user rights or wrong password (hopefully) no longer applies. The firewall tip will not help you either.

The following error message appears under Open installation Log:
MSI log file set to: C:\Users\rth\AppData\Local\Temp\setup.wix.msi.19.log
Windows Installer: Error: Service ‚Microsoft Dynamics 365 Business Central Server [BC140]‘ (MicrosoftDynamicsNavServer$BC140) failed to start. Verify that you have sufficient privileges to start system services.
Done configuring package. Id = ‚ServiceTier‘ ReturnCode = 1603
ERROR: Package Microsoft Dynamics 365 Business Central Server Components failed with error. See detailed message above.

But that doesn't help you either.

First step: I still recommend setting up the Navison server with a specially created service account (see above). If it just won't run, try it with the network service: uninstall the Nav server and reinstall it with the standard network service.

If you still can't get anywhere, use the "harmless" variant of the above "Power Off" version.

From your local Navision / Business Central setup DVD, access the service tier setup, e.g.

C:\temp\BC 14 Spring 2019\ServiceTier\Microsoft Dynamics 365 Business Central Server.msi

The setup program will again install the program according to the specifications you made earlier. You cannot change anything here!

But: This setup will also terminate with an error message. DO NOT confirm this message! Instead, use the task manager to terminate the setup task.
As a result, you will get a non-executable (there is still an error), but manageable Navision instance in your Business Central Administration. The really daring ones do this at the point where the Navision or Business Central setup wants to start the service (unsuccessfully):

After the reboot, you have a configurable service tier and can check in the event viewer what it doesn't like.

There is another alternative here, but you have to act quickly.

While the setup is running, the administration console is set up at some point, and the selected service is also set up in this.

And what if Navision / Business Central still won't install?

Now it's getting hairy. Now the setup has to be done completely by hand.

ServiceTier\FontsFolder\OCRB.ttf -> can be ignored.
ServiceTier\program files\Microsoft Dynamics NAV\140\Service -> Copy the entire directory content to C:\Program Files\Microsoft Dynamics 365 Business Central\140\Service ("140" adjust if necessary)
ServiceTier\System64Folder\NavSip.dll copy to C:\Windows\System32 and C:\Windows\SysWOW64

Now adjust the C:\Program Files\Microsoft Dynamics 365 Business Central\140\Service\CustomSettings.config . To do this, copy this file to c:\temp\ , for example, edit it there, then copy it back. It cannot be edited directly in folders because most text editors are not allowed to save there.
If you forget to do this in the heat of the moment, use "Save As" in, for example, c:\temp\ and then copy it back using Explorer.
You will find all the parameters that you would otherwise change graphically using the Console Business Central Administration.
So just the most important features, we just want to get it working.

<add key=“DatabaseServer“ value=“DESKTOP-UUSG2MN„/>
You can find "SqlComputerName" in the MS-SQL SMS, here DESKTOP-UUSG2MN

<add key=“DatabaseInstance“ value=“bcdemo„/>

In the above example, this would be BCDEMO. If you have not created a named instance (there is no \xxxx after the computer name), this will simply remain empty: value=""

<add key=“DatabaseName“ value=“ Demo Database BC (14-0)„/> Use this opportunity to give the SQL database a meaningful name.

<add key=“DatabaseUserName“ value=““/> – it is better to maintain this entry later using the credentials in the administration.
The reason: you get here
<add key=“ProtectedDatabasePassword“ value=““/> not so easy to enter the protected, encrypted password.

<add key=“ServerInstance“ value=“BC140„/> or a more meaningful name

Setting up the ports: If a change is necessary, it is best to only change the first two digits, e.g. 7046 > 8046 or 7046 -> 7146. Always leave the last two digits as they are, otherwise you will not be able to keep track of things later on when maintaining the system.

<add key=“ClientServicesPort“ value=“7046″/>
<add key=“SOAPServicesPort“ value=“7047″/>
<add key=“ODataServicesPort“ value=“7048″/>
<add key=“ManagementServicesPort“ value=“7045″/>
<add key=“ManagementServicesEnabled“ value=“true“/>
<add key=“ClientServicesEnabled“ value=“true“/>
<add key=“DeveloperServicesPort“ value=“7049″/>
<add key=“DeveloperServicesEnabled“ value=“false“/>
<add key=“DeveloperServicesSSLEnabled“ value=“false“/>

Now it's time to get down to business... The service must be registered. Both as a Windows service and as a Navision / Business Central administration service.

Let's start by registering the Navision service as a service.

You can also do that with Powershell, but I'm old...

In particular, the path and file name do NOT have to be typed in by hand. The first 1-2 letters are enough, then you can switch through the selection with TAB. By the way, this works for almost all commands on the command line. Not quite as elegant as in Powershell, but just as practical and fast. If you have gone past the target Microsoft.Dynamics.Nav.Server.exe, Shift+Tab will of course also take you back.

sc create MicrosoftDynamicsNavServer$BC14 binpath="c:\Program Files\Microsoft Dynamics 365 Business Central\140\Service\Microsoft.Dynamics.Nav.Server.exe $BC14" DisplayName="Microsoft Dynamics NAV Server365 Business Central Server [BC14]" start= delayed-auto type= own depend= HTTPService

Please pay attention to the spaces and quotation marks! BC14 stands for the instance name, which can of course be chosen freely. The exact version designation should also be noted, older Navision servers have different names. Ultimately, however, this is not decisive for the war, since the goal is to get an executable instance because the original setup fails.

The ServiceControll SC must report back with [SC] CreateService SUCCESS! Anything else indicates a typo.

You can also remove the newly installed service, e.g. if you made a typo:

sc delete MicrosoftDynamicsNavServer$BC14

or to have the installed services listed:

sc query

Do not close the command line prematurely! You can use the up arrow/down arrow to switch between the commands entered and thus correct/recycle an old entry. However, this also means that you CANNOT use the up arrow/down arrow to switch between lines in a long command line.

Set up via PowerShell

Since the installation was obviously unsuccessful, Navision or Business Central is missing the registry entries.

Copy the following block into an editor, customize the entries, save it as BC14.reg, for example, and import it into the registry by double-clicking.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Dynamics NAV]

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Dynamics NAV\100]

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Dynamics NAV\100\Web Components Prerequisites]
"Installed"="1"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Dynamics NAV\140]

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Dynamics NAV\140\Cronus Sample Database for SQL Server]
"CheckDatabase"="Demo Database BC (14-0)"
"DatabaseName"="Demo Database BC (14-0)"
"RestoreName"="Demo Database BC (14-0)"
"Broker"="Enabled"
"NavUser"="Interactive"
"Cronus"="NavUser"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Dynamics NAV\140\DynamicsNAV140Help]
"Installed"=dword:00000001
"FirewallException"=dword:00000001
"Name"="Microsoft Dynamics 365 Business Central Help"
"Path"="C:\\Inetpub\\wwwroot\\"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Dynamics NAV\140\Service]
"Installed"=dword:00000001
"FirewallException"=dword:00000001
"Path"="C:\\Program Files\\Microsoft Dynamics 365 Business Central\\140\\Service\\"

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Dynamics NAV\140\Web Client]
"Installed"=dword:00000001
"Name"="Microsoft Dynamics 365 Business Central Web Client"
"ApplicationPool"="Microsoft Dynamics 365 Business Central Web Client Application Pool"
"Path"="C:\\Program Files\\Microsoft Dynamics 365 Business Central\\140\\Web Client\\"

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Dynamics NAV]

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Dynamics NAV\100]

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Dynamics NAV\100\Web Components Prerequisites]
„Installed“=“1“

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Dynamics NAV\140]

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Dynamics NAV\140\Cronus Sample Database for SQL Server]
„CheckDatabase“=“Demo Database BC (14-0)“
„DatabaseName“=“Demo Database BC (14-0)“
„RestoreName“=“Demo Database BC (14-0)“
„Broker“=“Enabled“
„NavUser“=“Interactive“
„Cronus“=“NavUser“

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Dynamics NAV\140\DynamicsNAV140Help]
„Installed“=dword:00000001
„FirewallException“=dword:00000001
„Name“=“Microsoft Dynamics 365 Business Central Help“
„Path“=“C:\Inetpub\wwwroot\“

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Dynamics NAV\140\Service]
„Installed“=dword:00000001
„FirewallException“=dword:00000001
„Path“=“C:\Program Files\Microsoft Dynamics 365 Business Central\140\Service\“

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Dynamics NAV\140\Web Client]
„Installed“=dword:00000001
„Name“=“Microsoft Dynamics 365 Business Central Web Client“
„ApplicationPool“=“Microsoft Dynamics 365 Business Central Web Client Application Pool“
„Path“=“C:\Program Files\Microsoft Dynamics 365 Business Central\140\Web Client\“

Start a PowerShell in administrator mode:

Download the Navision / Business Central admin extension to the Powershell:

Import-Module 'C:\Program Files\Microsoft Dynamics 365 Business Central\140\Service\NavAdminTool.ps1'

(The Powershell makes it easier to enter the file path to the AdminTools via Intellisense)