docker - creating an image that restore database from .bak files.

Microsoft host a mssql docker image that use 'attach' method to mount database - this assume you have .mdf / ldf files available.

To deal with .bak files, it can be tricky. Hence i have created the following docker image and powershell scripts.

The docker file.

	FROM microsoft/mssql-server-windows-developer
	EXPOSE 1433
	ENV SA_PASSWORD=Password1!
	ENV ACCEPT_EULA=Y
	COPY mountdb.ps1 /
	WORKDIR /
	SHELL ["powershell", "-Command", "$ErrorActionPreference = 'SilentlyContinue';"]
	CMD ["powershell.exe", "c:\\mountdb.ps1"]

view raw Dockerfile_mssql hosted with ❤ by GitHub

This is not possible without "mountdb.ps1".  This scripts generate and runs restore command

	# Example environment configuratiopn
	# $targetRestorePath = "C:\MSSQLDATA\DATA\"
	# $env:restore_dbs = "[{'dbName':'ProductCatalog','DBLogicalName':'Bunnings.CommerceServer.ProductCatalog', 'Bakfilename':'C:\\mssqldata\\ProductCatalog.bak'}]"
	$targetRestorePath = $env:targetRestorePath
	$env:restore_dbs = "[{'dbName':'ProductCatalog','DBLogicalName':'Bunnings.CommerceServer.ProductCatalog', 'Bakfilename':'C:\\mssqldata\\ProductCatalog.bak'}]"
	$restore_dbs = $env:restore_dbs
	Write-Host "Database(s) to restore $restore_dbs to $targetRestorePath"
	$dbs = $restore_dbs | ConvertFrom-Json
	# Check if we have some environment configuration settings
	if ($null -ne $dbs -And $dbs.Length -gt 0)
	{
	Write-Verbose "Restoring database $($dbs.Length) database(s)"
	Foreach($db in $dbs)
	{
	$targetDbName = $db.dbName
	$targetDbBakFile = $db.Bakfilename
	$targetLogialDb = $db.DBLogicalName
	$targetLogialDbLog = $db.DBLogicalName + "_log"
	$sqlcmd = "RESTORE DATABASE [$targetDbName] FROM DISK = '$targetDbBakFile' WITH REPLACE, MOVE N'$targetLogialDb' TO N'$targetRestorePath$targetLogialDb" + ".mdf'," + " MOVE N'$targetLogialDbLog' TO N'$targetRestorePath$targetLogialDbLog" + ".ldf'"
	Write-Host $sqlcmd
	Write-Verbose "Invoke-Sqlcmd -Query $($sqlcmd)"
	Invoke-Sqlcmd -Query $sqlcmd -ServerInstance "localhost"
	}
	}
	Write-Host "Running image start scripts."
	.\start -sa_password $env:sa_password -ACCEPT_EULA $env:ACCEPT_EULA -attach_dbs \"$env:attach_dbs\" -Verbose

view raw mountdb.ps1 hosted with ❤ by GitHub

This scripts runs "Invoke-SqlCmd" to execute scripts and doesn't really required login.

It then runs, start.ps1 which change sa's password base on your environment settings.


Git repo can be found here.