Skip to content

Instantly share code, notes, and snippets.

@jeriley
Last active July 19, 2023 15:52
Show Gist options
  • Save jeriley/2f9ab76536edac2d78e554f44d80d31a to your computer and use it in GitHub Desktop.
Save jeriley/2f9ab76536edac2d78e554f44d80d31a to your computer and use it in GitHub Desktop.
MS sql local docker database restore
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
#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}
#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 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'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment