Having trouble with creating a BC Container with SQL 2022?

Introduction

If you're using Docker containers for development and have tried to restore a database that's compatible with SQL Server version 16, you've likely encountered an issue. There have been discussions about this problem, and there are several approaches to solve it. One approach is using SQL Server on the host, while the other is upgrading SQL Server in the container. I prefer the second method, and in this post, I'll outline what needs to be done. This article will provide a more detailed guide on how to implement this approach.

So, Let's try this then

Power shell script

# Helper functions
Set-ExecutionPolicy unrestricted -Force

Function Get-FileName($DialogTitle)
{   
   [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
   
   $OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
   $OpenFileDialog.Title = $DialogTitle
   $OpenFileDialog.InitialDirectory = 'C:\'
   $OpenFileDialog.Filter = 'All files (*.*)| *.*'
   $OpenFileDialog.ShowDialog() | Out-Null
   $OpenFileDialog.FileName
}

Function Get-Folder($initialDirectory)
{
    [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")|Out-Null

    $foldername = New-Object System.Windows.Forms.FolderBrowserDialog
    $foldername.Description = "Select a bak file download folder"
    $foldername.rootfolder = "MyComputer"

    if($foldername.ShowDialog() -eq "OK")
    {
        $folder += $foldername.SelectedPath
    }
    return $folder
}

#run this only
# Container main parameters
$ContainerName = Read-Host 'Enter container name'
$LicenseFile = Get-FileName('Select license file')

$AdditionalParameters = @()

if ($(Read-Host 'Restore from bak file (y or n)') -eq 'y') 
{
    $DatabaseName = Read-Host 'Enter database name'

    $connection_string = 'your connection string'  
    $container_name = 'backup'
    $subscription = 'your subscription'   

    $destination_path = Get-Folder
    
    Connect-AzAccount -Subscription $subscription
    Set-AzContext -Subscription $subscription
    $storage_account = New-AzStorageContext -ConnectionString $connection_string
    $blob = Get-AzStorageBlob -Container $container_name -Context $storage_account | Where-Object { $_.Name -like $DatabaseName +'*.bak' } | Sort-Object LastModified -Descending | Select-Object -First 1 Name,LastModified
    New-Item -ItemType Directory -Force -Path $destination_path  
    Get-AzStorageBlobContent -Container $container_name -Blob $blob.Name -Destination $destination_path -Context $storage_account  

    $BackupFile = $destination_path + '\' + $blob.Name
    $BackupFileName = Split-Path $BackupFile -Leaf
    $BackupFilePath = Split-Path $BackupFile -Parent
    $ContainerBackupPath = "C:\Backup" 
    $AdditionalParameters += '--volume {0}:{1}' -f $BackupFilePath, $ContainerBackupPath
}

$artifactUrl = Get-BCArtifactUrl -storageAccount bcartifacts -country w1 -select Closest OnPrem 22.0.54157.55195

# Fill parameters and run New-NavContainer
$Params = @{}
#$Params += @{ accept_eula = $true }
$Params += @{ artifactUrl = $artifactUrl }
$Params += @{ containerName = $ContainerName }
$Params += @{ licenseFile = $LicenseFile }
#$Params += @{ auth = 'NavUserPassword' }
#$Params += @{ credential = $Credential }
#$Params += @{ isolation = 'process' }
#$Params += @{ isolation = 'hyperv' }
#$Params += @{ memoryLimit = '4G' }
#$Params += @{ updateHosts = $true }
$Params += @{ accept_outdated  = $true }
$Params += @{ useBestContainerOS = $true }
$Params += @{ additionalParameters = $AdditionalParameters }

New-BcContainer @Params -shortcuts None
#run this only


#run this only
Enter-BcContainer -containerName $ContainerName
#run this only


#run this only
cd C:\dl\SQLServer2022-x64-ENU-Dev\
.\setup.exe /q /ACTION=Upgrade /IACCEPTSQLSERVERLICENSETERMS /INSTANCENAME=SQLEXPRESS /INDICATEPROGRESS

$env:PSModulePath = $env:PSModulePath+';C:\Program Files (x86)\Microsoft SQL Server\160\Tools\PowerShell\Modules\'
#run this only

#run this only
exit
#run this only


#run this only
Restore-DatabasesInBcContainer -containerName $ContainerName -bakFile $BackupFile -databaseName CRONUS

if ($(Read-Host 'Import fonts? (y or n)') -eq 'y') 
{
    Add-FontsToNavContainer -containername $ContainerName
    Restart-NavContainer -containerName $ContainerName
}
#run this only

Script should download bak file and create clean container