MS sql local docker database restore

addUser.sql

USE [master]
GO
CREATE LOGIN [<UserName>] WITH PASSWORD=N'<SomeKindOfStrongPassword>', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE [<DBName>]
GO
CREATE USER [<UserName>] FOR LOGIN [<UserName>]
GO
USE [<DBName>]
GO
ALTER ROLE [db_datareader] ADD MEMBER [<UserName>]
GO
USE [<DBName>]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [<UserName>]
GO

db-restore-bacpac.ps1

#Set-ExecutionPolicy Unrestricted -Scope Process

$containerName = ""
$dbName = ""
$localSqlSAPassword = ""

$azureDbServer = ""
$azureSqlUser = ""
$azureSqlPassword = ""

docker pull mcr.microsoft.com/mssql/server:2022-latest

docker kill ${containerName}
docker container rm ${containerName}

#restore backup
$scriptpath = $MyInvocation.MyCommand.Path
$dir = Split-Path $scriptpath
cd $dir

SqlPackage /Action:Export /TargetFile:"./${azureDbServer}-backup.bacpac" /SourceConnectionString:"Server=tcp:${azureDbServer}.database.windows.net,1433;Initial Catalog=${dbName};User ID=${azureSqlUser};Password=${azureSqlPassword};Persist Security Info=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;"

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=${localSqlSAPassword}" --name ${containerName} -p 1433:1433 -v /var/opt/mssql/backups -d mcr.microsoft.com/mssql/server:2022-latest

#wait for the container to spin up
Start-Sleep -Seconds 10

SqlPackage /Action:Import /SourceFile:".\${azureDbServer}-backup.bacpac" /TargetConnectionString:"Server=localhost;Initial Catalog=${dbName};User ID=sa;Password=${localSqlSAPassword};TrustServerCertificate=True;"

docker cp ./addUser.sql ${containerName}:/var/opt/mssql/backups
docker exec -it ${containerName} /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P ${localSqlSAPassword} -i "/var/opt/mssql/backups/addUser.sql"

#docker logs ${containerName}

db-restore-bak.ps1

#Set-ExecutionPolicy Unrestricted -Scope Process

$containerName = ""
$localSqlSAPassword = ""
$backupFileName = ""

docker pull mcr.microsoft.com/mssql/server:2022-latest

docker kill ${containerName}
docker container rm ${containerName}

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=${localSqlSAPassword}" --name ${containerName} -p 1433:1433 -v /var/opt/mssql/backups -d mcr.microsoft.com/mssql/server:2022-latest
Start-Sleep -Seconds 10

$scriptpath = $MyInvocation.MyCommand.Path
$dir = Split-Path $scriptpath
cd $dir

#restore backup

#copy files to container
docker cp ./${backupFileName} ${containerName}:/var/opt/mssql/backups
docker cp ./restore.sql ${containerName}:/var/opt/mssql/backups
docker cp ./addUser.sql ${containerName}:/var/opt/mssql/backups

#restore
docker exec -it ${containerName} /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P ${localSqlSAPassword} -i "/var/opt/mssql/backups/restore.sql"
docker exec -it ${containerName} /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P ${localSqlSAPassword} -i "/var/opt/mssql/backups/addUser.sql"

#docker logs ${containerName}

restore.sql

RESTORE DATABASE <DBName> FROM DISK = '/var/opt/mssql/backups/<DbBackupFile>.bak'
WITH 
MOVE '<DBName>' TO '/var/opt/mssql/data/<DBName>.mdf',
MOVE '<DBName>_log' TO '/var/opt/mssql/data/<DBName>.ldf'