Skip to content

Instantly share code, notes, and snippets.

@SQLvariant

SQLvariant/DockerDesktop-with-SQL-PowerShell-90.ipynb

Last active March 27, 2021 12:14
Show Gist options
  • Save SQLvariant/c22327141764fc75df952908b18dff1a to your computer and use it in GitHub Desktop.
Save SQLvariant/c22327141764fc75df952908b18dff1a to your computer and use it in GitHub Desktop.
Download ZIP
Create a SQL-on-Linux Docker Container with a Host Mount and Restore the AdventureWorks2016 Database
Display the source blob
Display the rendered blob
Raw
DockerDesktop-with-SQL-PowerShell-90.ipynb
{
"metadata": {
"kernelspec": {
"name": "powershell",
"display_name": "PowerShell"
},
"language_info": {
"name": "powershell",
"codemirror_mode": "shell",
"mimetype": "text/x-sh",
"file_extension": ".ps1"
}
},
"nbformat_minor": 2,
"nbformat": 4,
"cells": [
{
"cell_type": "markdown",
"source": [
"<img src=\"https://github.com/GodStorm91/mobydock/blob/master/mobydock/static/docker-logo.png?raw=true\" width=\"10%\"> ➕\r\n",
"<img src=\"https://github.com/Microsoft/azuredatastudio/blob/master/samples/notebookSamples/Graphics/AzureDataStudioLogo.png?raw=true\" width=\"6.5%\"> = ❤\r\n",
"\r\n",
"# This PowerShell Notebook will walk you through how to setup a SQL-on-Linux instance in a Docker container, on your local [Windows] machine."
],
"metadata": {
"azdata_cell_guid": "0f57848d-3a45-415f-9874-161eac860afc"
}
},
{
"cell_type": "markdown",
"source": [
"Note: You do need Docker Desktop installed for this to work. If you don't have it installed, you can use this Chocolatey command to install it:\r\n",
"\r\n",
"Note: You need to run that command above from an **elevated** PowerShell window (Run as administrator).\r\n",
"```powershell\r\n",
"choco install docker-desktop -y\r\n",
"```"
],
"metadata": {
"azdata_cell_guid": "41b4a48c-f224-42c1-a421-1e5ad5efd02b"
}
},
{
"cell_type": "markdown",
"source": [
"## Preparation Steps\r\n",
"0A) Before any of this can work, you must have Docker Destop running. \r\n",
" You must also have the latest SqlServer module installed from the PowerShell Gallery.\r\n",
"Install-Module SqlServer"
],
"metadata": {
"azdata_cell_guid": "e8c900fe-c443-4a1e-b86b-b59c17b642e6"
}
},
{
"cell_type": "code",
"source": [
"Install-Module SqlServer -Force;\r\n",
"Import-Module SqlServer;"
],
"metadata": {
"azdata_cell_guid": "12ca19b3-4b37-4409-a08a-8fc80c5bfd72"
},
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": ""
}
],
"execution_count": 1
},
{
"cell_type": "markdown",
"source": [
"0B) Use the code to download the AdventureWorks2016.bak file from GitHub"
],
"metadata": {
"azdata_cell_guid": "15a14e8d-c535-4e0a-9ac4-2d48fdfb8684"
}
},
{
"cell_type": "code",
"source": [
"$BakURL = \"https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2016.bak\";\r\n",
"$BakFile = \"$($Home)/Downloads/AdventureWorks2016.bak\";\r\n",
"Invoke-WebRequest -Uri $BakURL -OutFile $BakFile;"
],
"metadata": {
"azdata_cell_guid": "9970509e-857b-479e-9136-0859a5d2e74f"
},
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": "$BakURL = \"https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2016.bak\";\n$BakFile = \"$($Home)/Downloads/AdventureWorks2016.bak\";\nInvoke-WebRequest -Uri $BakURL -OutFile $BakFile;\n"
}
],
"execution_count": 35
},
{
"cell_type": "markdown",
"source": [
"0C) Create a local directory for the container to use to store database files, outside of the container."
],
"metadata": {
"azdata_cell_guid": "fcbd097d-4e89-48dc-9463-e8e9d77d3461"
}
},
{
"cell_type": "code",
"source": [
"mkdir C:/SQLData/Docker/SQLDev90"
],
"metadata": {
"azdata_cell_guid": "396f65ef-b04c-4f3c-8fe4-1a5c64ea829c"
},
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": "\n\n Directory: C:\\SQLData\\Docker\n\n\nMode LastWriteTime Length Name \n---- ------------- ------ ---- \nd----- 11/20/2019 9:44 AM SQLDev90 \n\n\n"
}
],
"execution_count": 1
},
{
"cell_type": "markdown",
"source": [
"## Create your SQL-on-Linux Docker Container\r\n",
"1) Create a SQL-on-Linux Docker Container with just the code below.\r\n",
" NOTE: You may want to change the password used for the SA account.\r\n",
" You may also want to change the Volumne path: \"C:\\SQLData\\Docker\\SQLDev90\""
],
"metadata": {
"azdata_cell_guid": "b5994870-dd5d-4e85-b266-607cbfb3f572"
}
},
{
"cell_type": "code",
"source": [
"docker run -d -p 10090:1433 -v C:\\SQLData\\Docker\\SQLDev90:/sqlserver -e ACCEPT_EULA=Y -e SA_PASSWORD=Test1234 --name testcontainer90 mcr.microsoft.com/mssql/server:2019-latest"
],
"metadata": {
"azdata_cell_guid": "7637f355-04b2-4214-95e4-f0c089d6e890"
},
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": "212bced0f344ccaab1de86ae616b0d4bb300a3e70932190713218eafcd5b6e88\n"
}
],
"execution_count": 17
},
{
"cell_type": "markdown",
"source": [
"### Verify that the SQL instance in the container is up & running\r\n",
"First, set a credential with the sa password you used. If you're running throught this notebook verbatim, that might be `Test1234`."
],
"metadata": {
"azdata_cell_guid": "859c0c6a-a935-42aa-889f-f1fed6815a27"
}
},
{
"cell_type": "code",
"source": [
"$SqlCred = (Get-Credential sa)"
],
"metadata": {
"azdata_cell_guid": "c424030b-1914-451d-8da0-b68f67b496be"
},
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": ""
}
],
"execution_count": 1
},
{
"cell_type": "markdown",
"source": [
"Now run the `Get-SqlDatabase` cmdlet against the container you created, and pass in the credential object you created in the previous step."
],
"metadata": {
"azdata_cell_guid": "a559679c-88fd-4a42-9dd1-48950560dcd9"
}
},
{
"cell_type": "code",
"source": [
"Get-SqlDatabase -ServerInstance 'localhost,10090' -Credential $SqlCred"
],
"metadata": {
"azdata_cell_guid": "9ee1eb47-52cd-44c1-ad0d-41bc259cddad"
},
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": "\nName Status Size Space Recovery Compat. Owner Collation \n Available Model Level \n---- ------ ---- ---------- -------- ------- ----- --------- \nmaster Normal 6.25 MB 608.00 KB Simple 150 sa SQL_Latin1_General_CP1\n _CI_AS \nmodel Normal 16.00 MB 5.70 MB Full 150 sa SQL_Latin1_General_CP1\n _CI_AS \nmsdb Normal 13.94 MB 96.00 KB Simple 150 sa SQL_Latin1_General_CP1\n _CI_AS \ntempdb Normal 72.00 MB 61.40 MB Simple 150 sa SQL_Latin1_General_CP1\n _CI_AS \n\n\n. {\n>> $SqlCred = (Get-Credential sa)\n>> Get-SqlDatabase -ServerInstance 'localhost,10090' -Credential $SqlCred\n>> }\n>> \n"
}
],
"execution_count": 4
},
{
"cell_type": "markdown",
"source": [
"2) Copy the backup file to the directory your container volume is mapped to.\r\n",
" Make sure you use the location you stored the .bak file in."
],
"metadata": {
"azdata_cell_guid": "d6943801-6718-46c1-af96-e0001a246c13"
}
},
{
"cell_type": "code",
"source": [
"Copy-Item -Path \"$($Home)\\Downloads\\AdventureWorks2016.bak\" -Destination C:\\SQLData\\Docker\\SQLDev90 -Verbose"
],
"metadata": {
"azdata_cell_guid": "570be45b-08a6-4034-83f8-034c72b31cf6"
},
"outputs": [
{
"name": "stdout",
"text": "",
"output_type": "stream"
}
],
"execution_count": 1
},
{
"cell_type": "markdown",
"source": [
"3) Restore the AdventureWorks2016 database"
],
"metadata": {
"azdata_cell_guid": "48afa7c6-f730-43fb-b298-4ff788d8ae9d"
}
},
{
"cell_type": "code",
"source": [
"Restore-SqlDatabase -ServerInstance 'localhost,10090' -Credential $SqlCred -BackupFile '/sqlserver/AdventureWorks2016.bak' -Database 'AdventureWorks2016' -AutoRelocateFile"
],
"metadata": {
"azdata_cell_guid": "7375976b-76fb-48f7-8906-9a04c9d00650"
},
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": ""
}
],
"execution_count": 15
},
{
"cell_type": "markdown",
"source": [
"Now verify the AdventureWorks 2016 database was successfully restored."
],
"metadata": {
"azdata_cell_guid": "f25043a9-b757-4b0b-a232-021883093af2"
}
},
{
"cell_type": "code",
"source": [
"Get-SqlDatabase -ServerInstance 'localhost,10090' -Credential $SqlCred"
],
"metadata": {
"azdata_cell_guid": "1eb402dd-c9c8-4b0a-ab2c-0a210e73e016"
},
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": "\nName Status Size Space Recovery Compat. Owner Collation \n Available Model Level \n---- ------ ---- ---------- -------- ------- ----- --------- \nAdventureWorks2016 Normal 209.63 MB 1.31 MB Simple 130 sa SQL_Latin1_General_CP1\n _CI_AS \nmaster Normal 6.25 MB 584.00 KB Simple 150 sa SQL_Latin1_General_CP1\n _CI_AS \nmodel Normal 16.00 MB 5.70 MB Full 150 sa SQL_Latin1_General_CP1\n _CI_AS \nmsdb Normal 15.56 MB 1.20 MB Simple 150 sa SQL_Latin1_General_CP1\n _CI_AS \ntempdb Normal 72.00 MB 61.33 MB Simple 150 sa SQL_Latin1_General_CP1\n _CI_AS \n\n\n"
}
],
"execution_count": 16
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment