Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Test script for SQL 2019 CTP 3.0
{
"metadata": {
"kernelspec": {
"name": "SQL",
"display_name": "SQL",
"language": "sql"
},
"language_info": {
"name": "sql",
"version": ""
}
},
"nbformat_minor": 2,
"nbformat": 4,
"cells": [
{
"cell_type": "markdown",
"source": "<pre>\r\nIf you don't already have a terminal window open, you need to first: <a href=\"command:workbench.action.terminal.focus\">Open the terminal</a> \r\n</pre>\r\n<pre> \r\nYou 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.)\r\n</pre>\r\n<pre>\r\n<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.\r\n</pre>\r\n<pre> \r\nAnother 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)\r\n</pre>",
"metadata": {}
}
]
}
#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.