Create a SQL-on-Linux Docker Container and Restore the AdventureWorks2016 Database

DockerDesktop-with-SQL-PowerShell-1.ps1

<# 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