Restoring production backup into tenant within container with V16

[scm6iwa]

Hi,

I have a working script to restore production data into docker container using SQL server express in the container for testing apps and it's been working fine for V15 versions of Business Central but now V16 has been released and when I try my script it errors with "The transaction operation cannot be performed because there are pending requests working on this transaction." when mounting the tenant database.

I had this issue when I restored the database into a hosted SQL server using 2019 RTM and I worked out that by upgrading to CU2 (latest version at the time) fixed the issue.

Any help why this has stopped working in V16 would be grateful.

This is my script

install-module navcontainerhelper -force

$auth = "UserPassword"
$credential = New-Object PSCredential 'admin', (ConvertTo-SecureString -String 'P@ssword1' -AsPlainText -Force)
$imageName = "mcr.microsoft.com/businesscentral/sandbox:gb-ltsc2019"
#$imageName = "mcr.microsoft.com/businesscentral/sandbox:15.4.41023.41932-gb-ltsc2019"
$containerName = "BC365"
$ServerInstance = "BC"

Remove-BCContainer $containerName

New-BCContainer `
    -accept_eula `
    -accept_outdated `
    -containerName $containerName `
    -imageName $imageName `
    -auth $auth `
    -Credential $credential `
    -multitenant `
    -alwaysPull `
    -updateHosts `
    -licenseFile 'c:\temp\7275031.flf' `
    -memoryLimit 10G

$tenantBacpac = "C:\ProgramData\NavContainerHelper\Production_20200326_03.bacpac"
$tenantId = "mydata"
$removeApps = @("Jet Library";"Jet Reports";"Jet Report Queries";"Job Queue Alert")
$RenameCompanyName = "LIVE"
$NewCompanyName = "CRONUS LIVE"
$DeleteCompanyName = 'Data-Template'

Invoke-ScriptInBCContainer -containerName $containerName -scriptblock { Param($tenantId, $tenantBacpac, $removeApps,$RenameCompanyName,$NewCompanyName,$DeleteCompanyName,$ServerInstance)
    Restore-BacpacWithRetry `
        -bacpac $tenantBacpac `
        -databasename $tenantId `
        -maxattempts 1

    $removeApps | ForEach-Object {
        Write-Host "USE [$tenantId] DELETE FROM [dbo].[NAV App Published App] WHERE Name = '$_' DELETE FROM [dbo].[NAV App Installed App] WHERE Name = '$_'"
        Invoke-Sqlcmd -Query "USE [$tenantId] DELETE FROM [dbo].[NAV App Published App] WHERE Name = '$_' DELETE FROM [dbo].[NAV App Installed App] WHERE Name = '$_'"
    }

    Write-Host "Mount $tenantId"
    Mount-NavTenant `
        -ServerInstance $ServerInstance `
        -id $tenantId `
        -databasename $tenantId `
        -databaseserver localhost `
        -databaseinstance SQLEXPRESS `
        -EnvironmentType Sandbox `
        -OverwriteTenantIdInDatabase `
        -Force

    Write-Host "Sync $tenantId"
    Sync-NavTenant `
        -ServerInstance $ServerInstance `
        -Tenant $tenantId `
        -Force

    Write-Host "Sync Apps"
    Get-NAVAppInfo -ServerInstance $ServerInstance -Tenant $tenantId | % { Sync-NAVApp -ServerInstance $ServerInstance -Tenant $tenantId -Name $_.Name -Version $_.Version }

    Write-Host "Start Data upgrade"
    Start-NavDataUpgrade -ServerInstance $ServerInstance -Tenant $tenantId -FunctionExecutionMode Serial -Force
    Get-NavDataUpgrade -ServerInstance $ServerInstance -Tenant $tenantId -Progress -Force

    Write-Host "Delete $DeleteCompanyName"
    Remove-NAVCompany -ServerInstance $ServerInstance -Tenant $tenantId -CompanyName $DeleteCompanyName -Force

    Write-Host "Rename $RenameCompanyName to $NewCompanyName"
    Rename-NAVCompany -ServerInstance $ServerInstance -Tenant $tenantId -CompanyName $RenameCompanyName -NewCompanyName $NewCompanyName -Force

    Write-Host "Delete users"
    Get-NAVServerUser -serverinstance $ServerInstance -Tenant $tenantId | % { if (-NOT ($_.UserName -eq 'IAN.ALDRIDGE')){Remove-NAVServerUser -ServerInstance $ServerInstance -Tenant $tenantId -UserName $_.UserName -Force }}

    Write-Host "USE [$tenantId] UPDATE [dbo].[Company] SET [Evaluation Company] = 1 WHERE (Name = '$NewCompanyName')"
    Invoke-Sqlcmd -Query "USE [$tenantId] UPDATE [dbo].[Company] SET [Evaluation Company] = 1 WHERE (Name = '$NewCompanyName')"

} -argumentList $tenantId, (Get-BCContainerPath -containerName $containerName -path $tenantBacpac), $removeApps, $RenameCompanyName, $NewCompanyName, $DeleteCompanyName, $ServerInstance

$credential = New-Object PSCredential 'ia', (ConvertTo-SecureString -String 'P@ssword1' -AsPlainText -Force)

New-NavContainerNavUser `
    -containerName $containerName `
    -Credential $credential `
    -ChangePasswordAtNextLogOn:$false `
    -PermissionSetId SUPER `
    -tenant $tenantId

Restart-NavContainer -containerName $containerName

And the output

Removing container BC365
Removing BC365 from host hosts file
Removing C:\ProgramData\NavContainerHelper\Extensions\BC365
NavContainerHelper is version 0.6.5.3
NavContainerHelper is running as administrator
Host is Microsoft Windows 10 Pro - 1909
Docker Client Version is 19.03.8
Docker Server Version is 19.03.8
Pulling image mcr.microsoft.com/businesscentral/sandbox:gb-ltsc2019
gb-ltsc2019: Pulling from businesscentral/sandbox
Using image mcr.microsoft.com/businesscentral/sandbox:gb-ltsc2019
Creating Container BC365
Version: 16.0.11240.11946-gb
Style: sandbox
Platform: 16.0.11233.11920
Generic Tag: 0.0.9.99
Container OS Version: 10.0.17763.973 (ltsc2019)
Host OS Version: 10.0.18363.752 (1909)
The container operating system does not match the host operating system, forcing hyperv isolation.
Using locale en-GB
Using hyperv isolation
Disabling the standard eventlog dump to container log every 2 seconds (use -dumpEventLog to enable)
Using license file c:\temp\7275031.flf
Files in C:\ProgramData\NavContainerHelper\Extensions\BC365\my:
- AdditionalOutput.ps1
- license.flf
- MainLoop.ps1
- SetupVariables.ps1
- updatehosts.ps1
Creating container BC365 from image mcr.microsoft.com/businesscentral/sandbox:gb-ltsc2019
3f0db6e71bb37e9805846936aae61818a4f0b3fd8c0de5dce3dfef358ba4157e
Waiting for container BC365 to be ready
Initializing...
Setting host.docker.internal to 192.168.1.3 in container hosts file (copy from host hosts file)
Setting gateway.docker.internal to 192.168.1.3 in container hosts file (copy from host hosts file)
Setting kubernetes.docker.internal to 127.0.0.1 in container hosts file (copy from host hosts file)
Setting host.containerhelper.internal to 172.22.144.1 in container hosts file
Starting Container
Hostname is BC365
PublicDnsName is BC365
Using NavUserPassword Authentication
Starting Local SQL Server
Starting Internet Information Server
Copying Database on localhost\SQLEXPRESS from Financialsgb to tenant
Taking database Financialsgb offline
Copying database files
Attaching files as new Database tenant
Putting database Financialsgb back online
Removing Database Financialsgb from localhost\SQLEXPRESS
Exporting Application to Financialsgb
Removing Application from tenant
Creating Self Signed Certificate
Self Signed Certificate Thumbprint 9EDDDB11FC29E520E58B9CF32A57A12C45027286
Modifying Service Tier Config File with Instance Specific Settings
Starting Service Tier
Registering event sources
Creating DotNetCore Web Server Instance
Enabling Financials User Experience
Using license file 'c:\run\my\license.flf'
Import License
Copying Database on localhost\SQLEXPRESS from tenant to default
Taking database tenant offline
Copying database files
Attaching files as new Database default
Putting database tenant back online
Mounting Database for default on server localhost\SQLEXPRESS
Sync'ing Tenant
Creating http download site
Setting SA Password and enabling SA
Creating admin as SQL User and add to sysadmin
WARNING: This license is not compatible with this version of Business Central.
Creating SUPER user
WARNING: This license is not compatible with this version of Business Central.
WARNING: This license is not compatible with this version of Business Central.
Container IP Address: 172.22.157.193
Container Hostname  : BC365
Container Dns Name  : BC365
Web Client          : http://BC365/BC/?tenant=default
Dev. Server         : http://BC365
Dev. ServerInstance : BC
Dev. Server Tenant  : default
Setting BC365 to 172.22.157.193 in host hosts file

Files:
http://BC365:8080/ALLanguage.vsix

Initialization took 115 seconds
Ready for connections!
Reading CustomSettings.config from BC365
Creating Desktop Shortcuts for BC365
Container BC365 successfully created
Restoring Database from C:\ProgramData\NavContainerHelper\Production_20200326_03.bacpac as mydata
USE [mydata] DELETE FROM [dbo].[NAV App Published App] WHERE Name = 'Jet Library' DELETE FROM [dbo].[NAV App Installed App] WHERE Name = 'Jet Library'
USE [mydata] DELETE FROM [dbo].[NAV App Published App] WHERE Name = 'Jet Reports' DELETE FROM [dbo].[NAV App Installed App] WHERE Name = 'Jet Reports'
USE [mydata] DELETE FROM [dbo].[NAV App Published App] WHERE Name = 'Jet Report Queries' DELETE FROM [dbo].[NAV App Installed App] WHERE Name = 'Jet Report Queries'
USE [mydata] DELETE FROM [dbo].[NAV App Published App] WHERE Name = 'Job Queue Alert' DELETE FROM [dbo].[NAV App Installed App] WHERE Name = 'Job Queue Alert'
Mount mydata
The following SQL error was unexpected:
The transaction operation cannot be performed because there are pending requests working on this transaction.

at <ScriptBlock>, <No file>: line 13
The following SQL error was unexpected:
The transaction operation cannot be performed because there are pending requests working on this transaction.
At C:\Program Files\WindowsPowerShell\Modules\navcontainerhelper\0.6.5.3\ContainerHandling\Invoke-ScriptInNavContainer.ps1:37 char:13
+             Invoke-Command -Session $session -ScriptBlock $scriptbloc ...
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (0:Int32) [Mount-NAVTenant], FaultException`1
    + FullyQualifiedErrorId : MicrosoftDynamicsNavServer$BC/mydata,Microsoft.Dynamics.Nav.Management.Cmdlets.MountNavTenant
    + PSComputerName        : 3f0db6e71bb37e9805846936aae61818a4f0b3fd8c0de5dce3dfef358ba4157e

[freddydk]

and your online tenant was already converted to 16.0?

[scm6iwa]

No it's at V15.4

[freddydk]

Then you will have to restore it to a 15.4 locally - and then upgrade to a 16.0 later using a normal upgrade mechanism. The above script is only mounting a tenant, doing a data upgrade.