Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Create a SQL-on-Linux Docker Container with a Host Mount and Restore the 5 AdventureWorks Databases
<# 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.
You may also want to change the Volumne path: "C:\SQLData\Docker\SQLDev02" #>
docker run -d -p 10002:1433 -v C:\SQLData\Docker\SQLDev02:/sqlserver -e ACCEPT_EULA=Y -e SA_PASSWORD=Test1234 --name testcontainer02 microsoft/mssql-server-linux
<# 2) Copy the backup file to the directory your container volume is mapped to.
Make sure you use the location you stored the .bak file in. #>
Copy-Item -Path "$($Home)\Downloads\AdventureWorks2016.bak" -Destination C:\SQLData\Docker\SQLDev02
<# 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. #>
Get-SqlDatabase -ServerInstance 'localhost,10002' -Credential (Get-Credential sa)
<# 3) Restore the AdventureWorks2016 database #>
#Requires -Modules SqlServer
$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile('AdventureWorks2016_Data', '/sqlserver/AdventureWorks2016_Data.mdf')
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile('AdventureWorks2016_Log', '/sqlserver/AdventureWorks2016_Log.ldf');
Restore-SqlDatabase -ServerInstance 'localhost,10002' -Credential (Get-Credential sa) -BackupFile '/sqlserver/AdventureWorks2016.bak' -Database 'AdventureWorks2016' -RelocateFile @($RelocateData,$RelocateLog)
<# Use the Get-SqlDatabase cmdlet to see all the databases on the instance. #>
Get-SqlDatabase -ServerInstance 'localhost,10002' -Credential (Get-Credential sa)
<# 4) This portion allows you to grab all four AdventureWorksDW sample databases from GitHub,
then downloads and the .bak file.
After the .bak files are downloaded you restore the instance specified. #>
$releases = Invoke-RestMethod https://api.github.com/repos/microsoft/sql-server-samples/releases
$BaksToDownload = ($releases | where { $_.name -eq 'AdventureWorks sample databases' -or $_.name -eq 'Wide World Importers sample database v1.0' }).assets |
WHERE { $_.name -like 'AdventureWorksDW201*bak' -and $_.name -notlike '*EXT*' } |
SELECT name, browser_download_url, size, updated_at
FOREACH ( $BakInfo in $BaksToDownload )
{
"$($BakInfo.name)";
Invoke-WebRequest -Uri $BakInfo.browser_download_url -OutFile "C:\SQLData\Docker\SQLDev02\$($BakInfo.name)"
Restore-SqlDatabase -ServerInstance 'localhost,10002' -Credential (Get-Credential sa) -BackupFile "/sqlserver/$($BakInfo.name)" -Database ($BakInfo.name -replace '.bak') -AutoRelocateFile
}
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.