Mounting a database from my online environment using SQL Server on the host

Posted on by

Update 2021/2/10: Microsoft stopped creating images for Docker in the summer of 2020. We now publish artifacts, which can be used to spin up containers and BcContainerHelper has replaced NavContainerHelper. This blog post reflects the old way of using NAV/BC on Docker and references NavContainerHelper, which is outdated.

This blog post is really a combination between the last two blog posts, https://freddysblog.com/2019/11/04/using-sql-server-on-the-host/ and https://freddysblog.com/2019/11/12/mounting-a-database-backup-from-my-online-environment-inside-a-container/. As stated in the last blog post, you can only use databases of less than 10Gb in size inside the container due to SQL Express. This blog post will explain how to get past that problem.

Please Note: Not for production

Please note that following this blog post will not give you an environment you can use for production. First of all, there might be extensions installed in your online environment, which you cannot install locally and furthermore Docker is not supported for production even if Docker would have been supported for production, we would definitely not support sandbox containers in production (as they simulate online and there are a number of things that doesn’t work in sandbox containers).

Prepare yourself

Please follow the steps outlined in this blog post to download your database backup in .bacpac format. You should also find out what image to use and again, I recommend using the latest sandbox image for the same reasons as described.

Create a multitenant container using SQL Server on the host

Like described in this blog post, we need to extract the database from the container and restore that on the SQL Server on the host.

Note that the database name is the containername-app. This serves the purpose that I can remove the container and all databases starting with containername- in order to start from scratch. Without this, I would only be able to have one multitenant container using the same SQL Server.

$containerName = "test"
$DatabaseFolder = "c:\databases"
$DatabaseName = "$containerName-app"

if (!(Test-Path $DatabaseFolder)) {
    New-Item $DatabaseFolder -ItemType Directory | Out-Null
}

if (Test-Path (Join-Path $DatabaseFolder "$($DatabaseName).*")) {
    Remove-BCContainer $containerName
    $databases = Invoke-Sqlcmd "SELECT name FROM master.sys.databases" | Where-Object { $_.name.StartsWith("$ContainerName-") }
    $databases | ForEach-Object {
        $dbname = $_.name
        Write-Host "Dropping database $dbname"
        Invoke-SqlCmd -Query "ALTER DATABASE [$dbname] SET OFFLINE WITH ROLLBACK IMMEDIATE" 
        Invoke-Sqlcmd -Query "DROP DATABASE [$dbname]"
        Write-Host "Removing Database files $($databaseFolder)\$($dbname).*"
        Remove-Item -Path (Join-Path $DatabaseFolder "$($dbname).*") -Force
    }
}

$imageName = Get-BestBCContainerImageName -imageName "mcr.microsoft.com/businesscentral/sandbox:us"
docker pull $imageName

$dbPath = Join-Path $env:TEMP ([Guid]::NewGuid().ToString())
Extract-FilesFromBCContainerImage -imageName $imageName -extract database -path $dbPath -force

$files = @()
Get-ChildItem -Path (Join-Path $dbPath "databases") | ForEach-Object {
    $DestinationFile = "{0}\{1}{2}" -f $databaseFolder, $DatabaseName, $_.Extension
    Copy-Item -Path $_.FullName -Destination $DestinationFile -Force
    $files += @("(FILENAME = N'$DestinationFile')")
}

Remove-Item -Path $dbpath -Recurse -Force

Write-Host "Attaching files as new Database $DatabaseName"
Write-Host "CREATE DATABASE [$DatabaseName] ON $([string]::Join(", ",$Files)) FOR ATTACH"
Invoke-SqlCmd -Query "CREATE DATABASE [$DatabaseName] ON $([string]::Join(", ",$Files)) FOR ATTACH"

After restoring the database to the host, we can create the container and much like in the previous blog post, we will create a container and specify database connection.

This time we will also specify -multitenant, but as you might know, this won’t change anything. When you specify a database connection, then I will assume that the database is ready – and not try to switch it into multitenancy mode. So we need a little more.

We need to override a few scripts. The SetupDatabase script (in order to prepare app and tenant databases) and the SetupTenant script (in order to mount the default tenant).

The last file we need to override is HelperFunctions.ps1. Here we will just use a newer version of HelperFunctions.ps1 directly from the nav-docker github repository, which fixes an issue with Copy-NavDatabase when running on a SQL Server on the host.

With the next version of the generic image, we don’t need to override HelperFunctions, but then again, it doesn’t hurt.

$setupDatabaseScript = @'
. 'C:\Run\SetupDatabase.ps1'
if (!$restartingInstance) {
    Copy-NavDatabase -databaseserver $DatabaseServer -databaseInstance $databaseInstance -databaseCredentials $databaseCredentials -sourcedatabaseName $databaseName -destinationDatabaseName "$(hostname)-$tenantId"
    Copy-NavDatabase -databaseserver $DatabaseServer -databaseInstance $databaseInstance -databaseCredentials $databaseCredentials -sourcedatabaseName $databaseName -destinationDatabaseName "$(hostname)-tenant"
}
'@

$setupTenantScript = @'
. 'c:\Run\SetupTenant.ps1'
if (!$restartingInstance) {
    Mount-NavDatabase -databaseserver $DatabaseServer -databaseInstance $databaseInstance -databaseCredentials $databaseCredentials -ServerInstance $ServerInstance -TenantId $TenantId -DatabaseName "$(hostname)-$tenantId"
}
'@

$credential = New-Object pscredential 'admin', (ConvertTo-SecureString -String 'P@ssword1' -AsPlainText -Force)
$dbcredentials = New-Object PSCredential -ArgumentList 'sa', $credential.Password
New-BCContainer `
    -accept_eula `
    -containerName $containerName `
    -imageName $imageName `
    -updateHosts `
    -auth UserPassword `
    -Credential $credential `
    -licensefile C:\temp\license.flf `
    -databaseServer 'host.containerhelper.internal' `
    -databaseInstance '' `
    -databaseName $DatabaseName `
    -databaseCredential $dbcredentials `
    -multitenant `
    -myScripts @(
        "https://raw.githubusercontent.com/microsoft/nav-docker/master/generic/Run/HelperFunctions.ps1"
        @{ "SetupDatabase.ps1" = $setupDatabaseScript }
        @{ "SetupTenant.ps1" = $setupTenantScript }
    )
New-NavContainerNavUser `
    -containerName $containerName `
    -Credential $credential `
    -ChangePasswordAtNextLogOn:$false `
    -PermissionSetId SUPER `
    -tenant 'Default'

With this, we will have a multitenant container using SQL Server on the host.

Not quite as easy as when running with the databases inside the container, but absolutely doable.

Note: The clever reader will notice that I do not Remove the application part from the tenant database and I do not remove the tenant part of the database from the app database. This is because the cmdlets for doing this only works with Windows Authentication and won’t work towards SQL Server on the host. For the purpose here, it doesn’t really matter – the container works just fine as is. If you need to clean up the app and tenant databases, you will have to extract the PowerShell cmdlets from the container and run them locally.

Restore the .bacpac

Now, we “just” need to restore and mount the .bacpac from our online environment. Restoring needs to be done on the host – and NOT in the container. You can do this using SSMS or you can use PowerShell. When using PowerShell, you need to install the Dac Framework on the host and you can find it here: https://go.microsoft.com/fwlink/?linkid=2108813.

we also need to make sure that the imported database is placed in the same folder as the other databases (c:\databases) and I can do this by reconfiguring the server through the smo dll.

The following script will restore the .bacpac and place the files in $databaseFolder:

$dacdll = Get-Item "C:\Program Files\Microsoft SQL Server\*\DAC\bin\Microsoft.SqlServer.Dac.dll"
if (!($dacdll)) {
    throw "Dac Framework is not installed, download and install from here: https://go.microsoft.com/fwlink/?linkid=2108813"
}
$smodll = Get-Item -Path "C:\Program Files\Microsoft SQL Server\*\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
if (!($smodll)) {
    throw "Unable to locate Microsoft.SqlServer.smo.dll. Is SQL Server installed on this machine?"
}

Add-Type -path $dacdll.FullName
Add-Type -Path $smodll.FullName

$tenantBacpac = "C:\ProgramData\NavContainerHelper\Production_20191110_02.bacpac"
$tenantId = "mydata"
$databaseName = "$containerName-$tenantId"
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($env:ComputerName)
$defaultFile = $server.Properties["DefaultFile"].Value
$defaultLog = $server.Properties["DefaultLog"].Value
$server.Properties["DefaultFile"].Value = $DatabaseFolder
$server.Properties["DefaultLog"].Value = $DatabaseFolder
$server.Alter()
try {
    $conn = "Data Source=localhost;Initial Catalog=master;Connection Timeout=0;Integrated Security=True;"
    Write-Host "Restoring Database from $tenantBacpac as $databaseName"
    $AppimportBac = New-Object Microsoft.SqlServer.Dac.DacServices $conn
    $ApploadBac = [Microsoft.SqlServer.Dac.BacPackage]::Load($tenantBacpac)
    $AppimportBac.ImportBacpac($ApploadBac, $DatabaseName)
}
finally {
    $server.Properties["DefaultFile"].Value = $defaultFile
    $server.Properties["DefaultLog"].Value = $defaultLog
    $server.Alter()
}

Publish/Remove extensions

Like with the other blog post, we need to install extensions in the container (if we have the source) or remove them from the database:

Publish-BCContainerApp -containerName $containerName -appFile "C:\Users\freddyk\Downloads\Freddy Kristiansen_BingMaps_15.0.56.0.app" -skipVerification -sync -install
$removeApps = @("MyApp")
$removeApps | ForEach-Object {
Invoke-Sqlcmd -Query "USE [$databaseName]
GO
DELETE FROM [dbo].[NAV App Published App]
WHERE Name = '$_'
DELETE FROM [dbo].[NAV App Installed App]
WHERE Name = '$_'
GO"
}

Mount and sync the database

Now, the database should be ready for mounting and sync’ing and the method to do this is similar to doing it in the container (just specifying database server, instance and credentials)

Invoke-ScriptInBCContainer -containerName $containerName -scriptblock { Param($tenantId, $databaseServer, $databaseInstance, $DatabaseName, $databaseCredential)
    Mount-NavTenant `
        -ServerInstance $ServerInstance `
        -id $tenantId `
        -databaseserver $databaseServer `
        -databaseinstance $databaseInstance `
        -databasename $databaseName `
    ​    -databaseCredential $databaseCredential `
        -EnvironmentType Sandbox `
        -OverwriteTenantIdInDatabase `
        -Force
    Sync-NavTenant `
        -ServerInstance $ServerInstance `
        -Tenant $tenantId `
        -Force
} -argumentList $tenantId, 'host.containerhelper.internal', '', $DatabaseName, $dbcredentials

Create the super user

Last but not least, create the super user with which you can login to the tenant:

New-NavContainerNavUser `
    -containerName $containerName `
    -credential $credential `
    -changePasswordAtNextLogOn:$false `
    -permissionSetId SUPER `
    -tenant $tenantId

and there you are:

Screenshot 2019-11-12 11.36.55

again – a LOT of PowerShell and you can probably stitch things together to work for you…

Enjoy

Freddy Kristiansen
Technical Evangelist

12 thoughts on “Mounting a database from my online environment using SQL Server on the host”

  1. Hi Freddy,

    First to thank you to put all those great powershell in blog, and always help me.

    We are trying to get our customer’s on cloud database to local so we can debug some strange behavior of our Apps, we are following steps from this post (with some adjustment to fit our environment). When we mount database, we got following error:
    (We are using latest release of NavContainerhelper 0.6.5.0)

    Unable to locate the server instance: MicrosoftDynamicsNavServer$mydata
    at , : line 2
    Unable to locate the server instance: MicrosoftDynamicsNavServer$mydata

    Here is detail of that powershell we run with error:
    PS I:\development\CAL2ALEXT> Invoke-ScriptInBCContainer -containerName $containerName -scriptblock { Param($tenantId, $databaseServer, $DatabaseName, $databaseCredential)
    Mount-NavTenant `
    -ServerInstance $ServerInstance `
    -id $tenantId `
    -databaseserver $databaseServer `
    -databasename $databaseName `
    ​ -databaseCredential $databaseCredential `
    -EnvironmentType Sandbox `
    -OverwriteTenantIdInDatabase `
    -Force
    Sync-NavTenant `
    -ServerInstance $ServerInstance `
    -Tenant $tenantId `
    -Force
    } -argumentList $tenantId, ‘host.containerhelper.internal’, $DatabaseName, $dbcredentials

    Unable to locate the server instance: MicrosoftDynamicsNavServer$mydata
    at , : line 2
    Unable to locate the server instance: MicrosoftDynamicsNavServer$mydata
    At C:\Program Files\WindowsPowerShell\Modules\navcontainerhelper\0.6.5.0\ContainerHandling\Invoke-ScriptInNavContainer.ps1:37 char:13
    + Invoke-Command -Session $session -ScriptBlock $scriptbloc …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (0:Int32) [Mount-NAVTenant], NavCommandException
    + FullyQualifiedErrorId : MicrosoftDynamicsNavServer$mydata/mydata,Microsoft.Dynamics.Nav.Management.Cmdlets.MountNavTenant
    + PSComputerName : 193d8d4da2d130ae2fea629e0f3c2a16654ba54688d7f5ab0520565b35a857c0

    Hope you can give me some hint.

    Thanks again.
    Tom

    Like

    Reply ↓

  2. It looks like the $ServerInstance variable inside the powershell session has been set to mydata, maybe used in some other scripting?
    You can use remove-navcontainersession to remove the session and have invoke-scriptinbccontainer create a new one – or you can use -serverinstance BC in the scripts above.

    Like

    Reply ↓

  3. Hi Freddy,

    Great post and I’ve managed to restore my production database but when I login I get this:

    Something went wrong.
    You cannot open the company because you do not have a paid licence for production companies.

    Any idea how I can change the company so it’s not classed as a production company in the restored database?

    Thanks

    Ian

    Like

    Reply ↓

      • Hi Freddy,

        I used this powershell script:

        $containerName = “BC365”
        $tenantId = “mydata”
        $CompanyName = ‘LIVE’
        $NewCompanyname = ‘CRONUS LIVE’

        Invoke-ScriptInBCContainer -containerName $containerName -scriptblock { Param($tenantId,$CompanyName,$NewCompanyName)
        Rename-NAVCompany -ServerInstance $ServerInstance -Tenant $tenantId -CompanyName $CompanyName -NewCompanyName $NewCompanyName -Force
        } -argumentList $tenantId,$CompanyName,$NewCompanyName

        and the company has been renamed but still has the same message.

        I’ve looked at the default Cronus company and it’s classed as an Evaluation company in the companies table and Demo Company in Company Information table. I have tried changing these in SQL but with no luck logging in.

        Any other pointers would be grateful.

        Thanks

        Ian

        Like

  4. Hi Freddy,

    I worked it out on how to change the company to a Demo company so I can login but there are quite a few extra steps I had to do due to my production environment is 15.2 and the container image is 15.3.

    This is my script I had to use which includes Sync-NAVApp and NavDataUpgrade after syncing the tenant:-

    install-module navcontainerhelper -force

    $containerName = “BC365”
    $DatabaseFolder = “c:\databases”
    $DatabaseName = “$containerName-app”
    $imageName = Get-BestBCContainerImageName -imageName “mcr.microsoft.com/businesscentral/sandbox:gb-ltsc2019”

    #Extract Db from Image and create it SQL Server on the host OS

    if (!(Test-Path $DatabaseFolder)) {
    New-Item $DatabaseFolder -ItemType Directory | Out-Null
    }

    if (Test-Path (Join-Path $DatabaseFolder “$($DatabaseName).*”)) {
    Remove-BCContainer $containerName
    $databases = Invoke-Sqlcmd “SELECT name FROM master.sys.databases” | Where-Object { $_.name.StartsWith(“$ContainerName-“) }
    $databases | ForEach-Object {
    $dbname = $_.name
    Write-Host “Dropping database $dbname”
    Invoke-SqlCmd -Query “ALTER DATABASE [$dbname] SET OFFLINE WITH ROLLBACK IMMEDIATE”
    Invoke-Sqlcmd -Query “DROP DATABASE [$dbname]”
    Write-Host “Removing Database files $($databaseFolder)\$($dbname).*”
    Remove-Item -Path (Join-Path $DatabaseFolder “$($dbname).*”) -Force
    }
    }

    docker pull $imageName

    $dbPath = Join-Path $env:TEMP ([Guid]::NewGuid().ToString())
    Extract-FilesFromBCContainerImage -imageName $imageName -extract database -path $dbPath -force

    $files = @()
    Get-ChildItem -Path (Join-Path $dbPath “databases”) | ForEach-Object {
    $DestinationFile = “{0}\{1}{2}” -f $databaseFolder, $DatabaseName, $_.Extension
    Copy-Item -Path $_.FullName -Destination $DestinationFile -Force
    $files += @(“(FILENAME = N’$DestinationFile’)”)
    }

    Remove-Item -Path $dbpath -Recurse -Force

    Write-Host “Attaching files as new Database $DatabaseName”
    Write-Host “CREATE DATABASE [$DatabaseName] ON $([string]::Join(“, “,$Files)) FOR ATTACH”
    Invoke-SqlCmd -Query “CREATE DATABASE [$DatabaseName] ON $([string]::Join(“, “,$Files)) FOR ATTACH”

    #Create Container and attach it to the database in SQL Server on the Host

    $setupDatabaseScript = @’
    . ‘C:\Run\SetupDatabase.ps1’
    if (!$restartingInstance) {
    Copy-NavDatabase -databaseserver $DatabaseServer -databaseInstance $databaseInstance -databaseCredentials $databaseCredentials -sourcedatabaseName $databaseName -destinationDatabaseName “$(hostname)-$tenantId”
    Copy-NavDatabase -databaseserver $DatabaseServer -databaseInstance $databaseInstance -databaseCredentials $databaseCredentials -sourcedatabaseName $databaseName -destinationDatabaseName “$(hostname)-tenant”
    }
    ‘@

    $setupTenantScript = @’
    . ‘c:\Run\SetupTenant.ps1’
    if (!$restartingInstance) {
    Mount-NavDatabase -databaseserver $DatabaseServer -databaseInstance $databaseInstance -databaseCredentials $databaseCredentials -ServerInstance $ServerInstance -TenantId $TenantId -DatabaseName “$(hostname)-$tenantId”
    }
    ‘@

    $credential = New-Object pscredential ‘admin’, (ConvertTo-SecureString -String ‘P@ssword1’ -AsPlainText -Force)
    $dbcredentials = New-Object PSCredential -ArgumentList ‘sa’, $credential.Password
    New-BCContainer `
    -accept_eula `
    -alwaysPull `
    -containerName $containerName `
    -imageName $imageName `
    -updateHosts `
    -auth NavUserPassword `
    -Credential $credential `
    -licensefile “C:\temp\Nav.flf” `
    -databaseServer ‘host.containerhelper.internal’ `
    -databaseInstance ” `
    -includeAL `
    -usessl:$false `
    -databaseName $DatabaseName `
    -databaseCredential $dbcredentials `
    -assignpremiumplan `
    -multitenant `
    -myScripts @(
    “https://raw.githubusercontent.com/microsoft/nav-docker/master/generic/Run/HelperFunctions.ps1”
    @{ “SetupDatabase.ps1” = $setupDatabaseScript }
    @{ “SetupTenant.ps1” = $setupTenantScript }
    )

    New-NavContainerNavUser `
    -containerName $containerName `
    -Credential $credential `
    -ChangePasswordAtNextLogOn:$false `
    -PermissionSetId SUPER `
    -tenant ‘Default’ `
    -assignpremiumplan `
    -databaseCredential $dbcredentials

    #Restore the bacpac backup into the SQL server database

    $dacdll = Get-Item “C:\Program Files\Microsoft SQL Server\*\DAC\bin\Microsoft.SqlServer.Dac.dll”
    if (!($dacdll)) {
    throw “Dac Framework is not installed, download and install from here: https://go.microsoft.com/fwlink/?linkid=2108813”
    }
    $smodll = Get-Item -Path “C:\Program Files\Microsoft SQL Server\*\SDK\Assemblies\Microsoft.SqlServer.Smo.dll”
    if (!($smodll)) {
    throw “Unable to locate Microsoft.SqlServer.smo.dll. Is SQL Server installed on this machine?”
    }

    Add-Type -path $dacdll.FullName
    Add-Type -Path $smodll.FullName

    $tenantBacpac = “C:\ProgramData\NavContainerHelper\Production_20200219_02.bacpac”
    $tenantId = “mydata”
    $removeApps = @(“Jet Library”;”Jet Reports”;”Jet Report Queries”;”Job Queue Alert”)
    $databaseName = “$containerName-$tenantId”
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server($env:ComputerName)
    $defaultFile = $server.Properties[“DefaultFile”].Value
    $defaultLog = $server.Properties[“DefaultLog”].Value
    $server.Properties[“DefaultFile”].Value = $DatabaseFolder
    $server.Properties[“DefaultLog”].Value = $DatabaseFolder
    $server.Alter()
    try {
    $conn = “Data Source=localhost;Initial Catalog=master;Connection Timeout=0;Integrated Security=True;”
    Write-Host “Restoring Database from $tenantBacpac as $databaseName”
    $AppimportBac = New-Object Microsoft.SqlServer.Dac.DacServices $conn
    $ApploadBac = [Microsoft.SqlServer.Dac.BacPackage]::Load($tenantBacpac)
    $AppimportBac.ImportBacpac($ApploadBac, $DatabaseName)
    }
    finally {
    $server.Properties[“DefaultFile”].Value = $defaultFile
    $server.Properties[“DefaultLog”].Value = $defaultLog
    $server.Alter()
    }

    #Publish the App extensions into the container

    Publish-BCContainerApp -containerName $containerName -appFile “C:\Users\Test\Documents\Test App.app” -skipVerification -sync -install

    #Remove App extensions from the container

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

    Write-Host “Mount Teneant”

    Invoke-ScriptInBCContainer -containerName $containerName -scriptblock { Param($tenantId, $databaseServer, $ServerInstance, $DatabaseName, $databaseCredential, $databaseInstance)
    Mount-NavTenant -ServerInstance $ServerInstance -id $tenantId -databaseserver $databaseServer -databaseinstance $databaseInstance -databasename $databaseName -databaseCredential $databaseCredential -EnvironmentType Sandbox -OverwriteTenantIdInDatabase -AllowAppDatabaseWrite -Force
    } -argumentList $tenantId, ‘host.containerhelper.internal’, ‘BC’, $DatabaseName, $dbcredentials, ”

    Write-Host “Sync Teneant”

    Invoke-ScriptInBCContainer -containerName $containerName -scriptblock { Param($tenantId, $databaseServer, $ServerInstance, $DatabaseName, $databaseCredential)
    Sync-NavTenant -ServerInstance $ServerInstance -Tenant $tenantId -Force
    } -argumentList $tenantId, ‘host.containerhelper.internal’, ‘BC’, $DatabaseName, $dbcredentials

    Write-Host “Sync Apps Tenant”

    Invoke-ScriptInBCContainer -containerName $containerName -scriptblock { Param($tenantId, $databaseServer, $ServerInstance, $DatabaseName, $databaseCredential)
    Get-NAVAppInfo -ServerInstance $ServerInstance -Tenant $tenantId | % { Sync-NAVApp -ServerInstance $ServerInstance -Tenant $tenantId -Name $_.Name -Version $_.Version }
    } -argumentList $tenantId, ‘host.containerhelper.internal’, ‘BC’, $DatabaseName, $dbcredentials

    Invoke-ScriptInBCContainer -containerName $containerName -scriptblock { Param($tenantId, $ServerInstance)
    Start-NavDataUpgrade -ServerInstance $ServerInstance -Tenant $tenantId -FunctionExecutionMode Serial -Force
    } -argumentList $tenantId, ‘BC’

    Invoke-ScriptInBCContainer -containerName $containerName -scriptblock { Param($tenantId, $ServerInstance)
    Get-NavDataUpgrade -ServerInstance $ServerInstance -Tenant $tenantId -Progress -Force
    } -argumentList $tenantId, ‘BC’

    Write-Host “Sleep 60”

    start-sleep -Seconds 60

    $CompanyName1 = ‘DEV’

    Write-Host “Delete $CompanyName1”

    Invoke-ScriptInBCContainer -containerName $containerName -scriptblock { Param($tenantId,$CompanyName1,$ServerInstance)
    Remove-NAVCompany -ServerInstance $ServerInstance -Tenant $tenantId -CompanyName $CompanyName1 -Force
    } -argumentList $tenantId,$CompanyName1,’BC’

    $CompanyName = “LIVE”

    Write-Host “Rename $CompanyName to CRONUS LIVE”

    Invoke-ScriptInBCContainer -containerName $containerName -scriptblock { Param($tenantId,$CompanyName,$NewCompanyName,$ServerInstance)
    Rename-NAVCompany -ServerInstance $ServerInstance -Tenant $tenantId -CompanyName $CompanyName -NewCompanyName $NewCompanyName -Force
    } -argumentList $tenantId,$CompanyName,’CRONUS LIVE’,’BC’

    $credential = New-Object pscredential ‘Admin1’, (ConvertTo-SecureString -String ‘P@ssword1’ -AsPlainText -Force)

    New-NavContainerNavUser `
    -containerName $containerName `
    -credential $credential `
    -changePasswordAtNextLogOn:$false `
    -permissionSetId SUPER `
    -tenant $tenantId `
    -assignpremiumplan `
    -databaseCredential $dbcredentials

    Write-Host “USE [$databaseName] UPDATE [dbo].[Company] SET [Evaluation Company] = 1 WHERE Name = ‘CRONUS LIVE'”
    Invoke-Sqlcmd -Query “USE [$databaseName] UPDATE [dbo].[Company] SET [Evaluation Company] = 1 WHERE Name = ‘CRONUS LIVE'”

    Restart-NavContainer -containerName $containerName

    Hope this will help anyone else having issues.

    Ian

    Like

    Reply ↓

  5. Hi Freddy,

    Just to let you know that I’ve tried restoring production data into the mydata tenant via the script using version 16.0.11240.11946-gb-ltsc2019 and it comes up with this error when mounting the tenant database:

    “The transaction operation cannot be performed because there are pending requests working on this transaction.”.

    I have tried using 15.4.41023.41932-gb-ltsc2019 and all works fine. I’ve also tried using the SQL server Express in the container itself and it comes up with the same issue using V16.

    Do you know if there are any issues with the first version of 16 on the SQL Server side?

    Regards

    Ian

    Like

    Reply ↓

  6. Pingback: Restoring your online Business Central database locally | Freddys blog

  7. Pingback: Restoring your online Business Central database locally - Freddy's Blog - Dynamics 365 Business Central/NAV User Group - Dynamics User Group

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s