Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Test script for SQL 2019 CTP 3.0
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
#Requires -Modules SqlServer
<# 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
#>
Import-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\SQLDev63" #>
docker run -d -p 10063:1433 -v C:\SQLData\Docker\SQLDev63:/sqlserver -e ACCEPT_EULA=Y -e SA_PASSWORD=Test1234 --name testcontainer63 mcr.microsoft.com/mssql/server:2019-CTP3.0-ubuntu
<# 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\SQLDev63
<# 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. #>
$Sql63Cred = (Get-Credential sa)
#Get-SqlDatabase -ServerInstance 'localhost,10063' -Credential $Sql63Cred
#docker exec -it testcontainer63 bash
Start-Sleep -Seconds 4
<# 3) Restore the AdventureWorks2016 database #>
Restore-SqlDatabase -ServerInstance 'localhost,10063' -Credential $Sql63Cred -BackupFile '/sqlserver/AdventureWorks2016.bak' -Database 'AdventureWorks2016' -AutoRelocateFile
<# Use the Get-SqlDatabase cmdlet to see all the databases on the instance. #>
Get-SqlDatabase -ServerInstance 'localhost,10063' -Credential $Sql63Cred
<# 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\SQLDev63\$($BakInfo.name)"
Restore-SqlDatabase -ServerInstance 'localhost,10063' -Credential $Sql63Cred -BackupFile "/sqlserver/$($BakInfo.name)" -Database ($BakInfo.name -replace '.bak') -AutoRelocateFile
};
Get-SqlDatabase -ServerInstance 'localhost,10063' -Credential $Sql63Cred
<# Clean-up:
docker stop testcontainer63
docker rm testcontainer63
dir -Path C:\SQLData\Docker\SQLDev63 -Filter AdventureWorks*.bak | Remove-Item
#>
<pre>
If you don't already have a terminal window open, you need to first: <a href="command:workbench.action.terminal.focus">Open the terminal</a>
</pre>
<pre>
You probably don't have this directory on your machine, so run this: <a href="command:workbench.action.terminal.sendSequence?%7B%22text%22%3A%22mkdir%20C:%2FSQLData%2FDocker%2FSQLDev63%22%7D">mkdir C:/SQLData/Docker/SQLDev63 </a> (Yes, it works with the slashes in that direction.)
</pre>
<pre>
<a href="command:workbench.action.terminal.sendSequence?%7B%22text%22%3A%22Invoke-Expression%20(Invoke-WebRequest%20https:%2F%2Fgist.githubusercontent.com%2FSQLvariant%2F63193826e2352f2a8c1c85f63c724501%2Fraw%2Fd424e4127ceda141cc091aa7cfba7a11e410143e/DockerDesktop-with-SQL-PowerShell-63.ps1)%22%7D">Spin up a Docker Container with Invoke-Expression (Invoke-WebRequest https://gist.githubusercontent.com/SQLvariant) </a> Just click enter after the command is placed into the terminal. When prompted, the sa password is Test1234, but you can obviously change this.
</pre>
<pre>
Another example: <a href="command:workbench.action.terminal.sendSequence?%7B%22text%22%3A%22Invoke-Expression%20(Invoke-WebRequest%20https:%2F%2Faka%2Ems%2Frstools)%22%7D">Download RSTools Module </a>using Invoke-Expression (Invoke-WebRequest https://aka.ms/rstools)
</pre>
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.