The end game is to create a database when building a docker container, and persist the data so that if the container is removed, I can start the container again and have my database with my persisted data.
I'm using microsoft/mssql-server-windows-developer with Windows containers with docker-compose.
The relevant part of my docker-compose file is (other services removed):
version: "3.9" services:
db:
build:
context: .
dockerfile: Database/Dockerfile
volumes:
- C:\temp:C:\temp
ports:
- 1433:1433
Basically, the db Dockerfile runs a powershell script (very similar to https://github.com/microsoft/mssql-docker/blob/master/windows/mssql-server-windows-developer/start.ps1). My powershell script starts MSSQLSERVER then runs sql files to create a database, run create table, create procs, etc scripts.
All of this works. docker-compose build then docker-compose up will create and run my database on localhost and everything is great. But, if I manipulate the data at all and remove the database then call docker-compose up again, my data is gone.
Everything I've read about persisting data includes using attach_db. I would like to do some sort of if exists, attach_db else create database.
The question (finally)... Why don't I have an mdf file after I create the database? Am I supposed to? I've messed with different ways to add volumes but my volume is always empty. It doesn't appear I'm creating an mdf file to add to my volume.
EDIT - Adding Dockerfile and ps script Dockerfile calls
Dockerfile:
FROM microsoft/mssql-server-windows-developer
ENV sa_password="nannynannybooboo" \
ACCEPT_EULA="Y" \
db1="db1" \
db2="db2"
EXPOSE 1433
RUN mkdir -p ./db1
RUN mkdir -p ./db2
COPY /Database/startsql.ps1 .
COPY /Database/db1/ ./db1
COPY /Database/db2/ ./db2
HEALTHCHECK CMD [ "sqlcmd", "-Q", "select 2" ]
RUN .\startsql -sa_password $env:sa_password -ACCEPT_EULA $env:ACCEPT_EULA -db_name $env:db2 -Verbose
RUN .\startsql -sa_password $env:sa_password -ACCEPT_EULA $env:ACCEPT_EULA -db_name $env:db1 -Verbose
startsql.ps1
# based off https://github.com/microsoft/mssql-docker/blob/master/windows/mssql-server-windows-developer/start.ps1
param(
[Parameter(Mandatory=$false)]
[string]$sa_password,
[Parameter(Mandatory=$false)]
[string]$ACCEPT_EULA,
[Parameter(Mandatory=$true)]
[string]$db_name
)
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"
}
}
Write-Verbose $sa_password
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
}
Write-Verbose "Started SQL Server"
Write-Verbose "Starting set up scripts..."
Write-Verbose $db_name
$exists = $true
$exists = @($sqlServer.Databases | % { $_.Name }) -contains $db_name
$creation = ".\"+$db_name+"\creation.sql"
$creation_rpt = ".\"+$db_name+"\creation.rpt"
$userdefined = ".\"+$db_name+"\userdefined.sql"
$userdefined_rpt = ".\"+$db_name+"\userdefined.rpt"
$presetup = ".\"+$db_name+"\pre.setup.sql"
$presetup_rpt = ".\"+$db_name+"\presetup.rpt"
$tables = ".\"+$db_name+"\tables.sql"
$tables_rpt = ".\"+$db_name+"\tables.rpt"
$procs = ".\"+$db_name+"\procs.sql"
$procs_rpt = ".\"+$db_name+"\procs.rpt"
$triggers = ".\"+$db_name+"\triggers.sql"
$triggers_rpt = ".\"+$db_name+"\triggers.rpt"
Write-Verbose $creation
Write-Verbose $exists
if ($exists -ne $true){
Write-Verbose "Starting creation script..."
Invoke-Sqlcmd -InputFile $creation | Out-File -FilePath $creation_rpt
Write-Verbose "Starting user defined script..."
Invoke-Sqlcmd -InputFile $userdefined | Out-File -FilePath $userdefined_rpt
Write-Verbose "Starting pre.setup script..."
Invoke-Sqlcmd -InputFile $presetup | Out-File -FilePath $presetup_rpt
Write-Verbose "Starting tables script..."
Invoke-Sqlcmd -InputFile $tables | Out-File -FilePath $tables_rpt
Write-Verbose "Starting triggers script..."
Invoke-Sqlcmd -InputFile $triggers | Out-File -FilePath $triggers_rpt
Write-Verbose "Starting procs script..."
Invoke-Sqlcmd -InputFile $procs | Out-File -FilePath $procs_rpt
}
Get-EventLog -LogName Application -Source "MSSQL*" -After (Get-Date).AddSeconds(-2) | Select-Object TimeGenerated, EntryType, Message
I can't share the sql files startsql calls, but 99% of the sql is SSMS generate scripts from an existing DB that I am replicating. The 1% that isn't generated by SSMS is a command to link the two databases being created.
