Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Create a SQL-on-Linux Docker Container and Restore the AdventureWorks2016 Database
<# 0A) Before any of this can work, you must have Docker Destop running.
You must also have the latest SqlServer module installed from the PowerShell Gallery.#>
Install-Module SqlServer
<# 0B) Use this code to download the AdventureWorks2016.bak file from GitHub: #>
$BakURL = "https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2016.bak";
$BakFile = "$($Home)/Downloads/AdventureWorks2016.bak";
Invoke-WebRequest -Uri $BakURL -OutFile $BakFile;
<# 1) Create a SQL-on-Linux Docker Container with just the code below.
NOTE: You may want to change the password used for the SA account. #>
docker run -d -p 10001:1433 -e ACCEPT_EULA=Y -e SA_PASSWORD=Test1234 --name testcontainer01 microsoft/mssql-server-linux
<# 2) Check to make sure you can successfully connect to your new SQL instance runnin in a Container.
Note: you should only see the four system databases at this point. #>
#Requires -Modules SqlServer
Invoke-Sqlcmd -ServerInstance 'localhost,10001' -Credential (Get-Credential sa) -Query "SELECT name from sys.databases"
<# 3) Copy the backup file into your container.
Make sure you use the location you stored the .bak file in. #>
docker cp "$($Home)/Downloads/AdventureWorks2016.bak" testcontainer01:'var/opt/mssql/data/'
<# 4) You have to do this next part from a PowerShell console, not ISE #>
docker exec -it testcontainer01 bash
<# 5) Using bash, make sure you can see the AdventureWorks2016.bak file inside the container. #>
ls var/opt/mssql/data/
<# NOT: If you were doing all of this in Azure Data Studio, be sure to Exit bash. #>
exit
<# 6A) Run one of the following blocks of code, but not both, to restore the Adventureworks2016 database to your new container instance. #>
$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile('AdventureWorks2016_Data', '/var/opt/mssql/data/AdventureWorks2016_Data.mdf');
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile('AdventureWorks2016_Log', '/var/opt/mssql/data/AdventureWorks2016_Log.ldf');
Restore-SqlDatabase -ServerInstance 'localhost,10001' -Credential (Get-Credential sa) -BackupFile 'AdventureWorks2016.bak' -Database 'AdventureWorks2016' -RelocateFile @($RelocateData,$RelocateLog);
<# 6B Invoke-Sqlcmd option #>
#Requires -Modules SqlServer
Invoke-Sqlcmd -ServerInstance 'localhost,10001' -Username sa -Password Test1234 -Query "
RESTORE DATABASE [AdventureWorks2016] FROM DISK = N'/var/opt/mssql/data/AdventureWorks2016.bak' WITH FILE = 1,
MOVE N'AdventureWorks2016_Data' TO N'/var/opt/mssql/data/AdventureWorks2016_Data.mdf',
MOVE N'AdventureWorks2016_Log' TO N'/var/opt/mssql/data/AdventureWorks2016_Log.ldf',
NOUNLOAD, STATS = 5"
<# Verify the AdventureWorks2016 database was restored. #>
Invoke-Sqlcmd -ServerInstance 'localhost,10001' -Credential (Get-Credential sa) -Query "
SELECT name from sys.databases"
<#################################################################################################################>
# PLEASE NOTE: The following commands are included for destroying the container so you can create it again.
<#################################################################################################################>
<# Just stop the container so you can use it again later. #>
docker stop testcontainer01
<# Destroy the container. #>
docker rm testcontainer01
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.