How to create MSSQL Image With Custom DBs Attached (using BAK files)

The following Dockerfile can be used to create the custom image, using a local context.

Note: This uses the MSSQL base image shown here.

The example shows how to add one custom database called Audit. The corresponding audit.bak needs to be in the same folder as the docker file:

FROM deanobrien/sql2019:empty

ENV sa_password="xxx" \
    ACCEPT_EULA="Y" \
    sa_password_path="C:\ProgramData\Docker\secrets\sa-password" \
    attach_dbs='[{"dbName":"10_Corp_Sitecore_audit","logicalName":"Corp_Sitecore_audit","backupFile":"c:\\Custom\\10_Corp_Sitecore_audit.bak"}]'
SHELL ["powershell", "-Command", "$ErrorActionPreference = 'Stop'; $ProgressPreference = 'SilentlyContinue';"]

RUN mkdir C:/Custom/
RUN mkdir C:/Data/

COPY audit.bak C:/Custom 

WORKDIR /

COPY start-or-restore.ps1 /

HEALTHCHECK CMD [ "sqlcmd", "-Q", "select 1" ]

CMD .\start-or-restore -sa_password $env:sa_password -ACCEPT_EULA $env:ACCEPT_EULA -attach_dbs \"$env:attach_dbs\" -Verbose

You also need to create a powershell file called start-or-restore.ps1 and add the following content:

param(
[Parameter(Mandatory=$false)]
[string]$sa_password,

[Parameter(Mandatory=$false)]
[string]$ACCEPT_EULA,

[Parameter(Mandatory=$false)]
[string]$attach_dbs
)


if($ACCEPT_EULA -ne "Y" -And $ACCEPT_EULA -ne "y")
{
	Write-Verbose "ERROR: You must accept the End User License Agreement before this container can start."
	Write-Verbose "Set the environment variable ACCEPT_EULA to 'Y' if you accept the agreement."

    exit 1
}

# start the service
Write-Verbose "Starting SQL Server"
start-service MSSQLSERVER

if($sa_password -eq "_") {
    if (Test-Path $env:sa_password_path) {
        $sa_password = Get-Content -Raw $secretPath
    }
    else {
        Write-Verbose "WARN: Using default SA password, secret file not found at: $secretPath"
    }
}

if($sa_password -ne "_")
{
    Write-Verbose "Changing SA login credentials"
    $sqlcmd = "ALTER LOGIN sa with password=" +"'" + $sa_password + "'" + ";ALTER LOGIN sa ENABLE;"
    & sqlcmd -Q $sqlcmd
}

$attach_dbs_cleaned = $attach_dbs.TrimStart('\\').TrimEnd('\\')

$dbs = $attach_dbs_cleaned | ConvertFrom-Json

if ($null -ne $dbs -And $dbs.Length -gt 0)
{
    Write-Verbose "Restoring $($dbs.Length) database(s)"
	    
    Foreach($db in $dbs) 
    {      
		$databaseName=$db.dbName
	    if (Test-Path "c:\Data\$databaseName.mdf") {
			Write-Verbose "Database $databaseName already exists, SKIPPING restore."
		}
		else
		{
			Write-Verbose "RESTORING Database $databaseName ."
			if($databaseName -like "*audit*")
			{
				Write-Verbose "Contains audit"
				$sqlcmd = "IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = '" + $($db.dbName) + "') BEGIN EXEC sp_detach_db [$($db.dbName)] END;RESTORE DATABASE [$($db.dbName)] FROM DISK = '$($db.backupFile)' WITH RECOVERY, MOVE '$($db.logicalName)' TO 'c:\Data\$($db.dbName).mdf', MOVE '$($db.logicalName)_Log' TO 'c:\Data\$($db.dbName)_Log.ldf', MOVE '$($db.logicalName)2' TO 'c:\Data\$($db.dbName)2.ndf', MOVE '$($db.logicalName)3' TO 'c:\Data\$($db.dbName)3.ndf';"			
			}
			else
			{
				Write-Verbose "$databaseName didnt contain any of NDF DBs"
				$sqlcmd = "IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = '" + $($db.dbName) + "') BEGIN EXEC sp_detach_db [$($db.dbName)] END;RESTORE DATABASE [$($db.dbName)] FROM DISK = '$($db.backupFile)' WITH RECOVERY, MOVE '$($db.logicalName)' TO 'c:\Data\$($db.dbName).mdf', MOVE '$($db.logicalName)_Log' TO 'c:\Data\$($db.dbName)_Log.ldf';"			
			}
			Write-Verbose "Invoke-Sqlcmd -Query $($sqlcmd)"
			& sqlcmd -Q $sqlcmd
		}
	}
}

Write-Verbose "Started SQL Server."

$lastCheck = (Get-Date).AddSeconds(-2) 
while ($true) 
{ 
    Get-EventLog -LogName Application -Source "MSSQL*" -After $lastCheck | Select-Object TimeGenerated, EntryType, Message	 
    $lastCheck = Get-Date 
    Start-Sleep -Seconds 2 
}

Note: You will need to adjust the file above depending on way your database is stored on disk. Some databases have multiple LDF / NDF files etc. So the sqlcmd above will need to be updated to reflect this. In the example above, the audit database has an MDF, LDF and 2 x NDF files.

Build using docker build command

To create the image, you need to save the above as a file called Dockerfile and run the following command:

 docker build -t deanobrien/sql2019:audit .

Where ‘deanobrien’ is the name of the repository where we will store the resulting image.

You would then need to push the image manually to the registry.