Skip to content

Instantly share code, notes, and snippets.

@SQLvariant
Last active June 22, 2019 00:11
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save SQLvariant/c6822f7915a951943c2bf0b750a3ba92 to your computer and use it in GitHub Desktop.
Save SQLvariant/c6822f7915a951943c2bf0b750a3ba92 to your computer and use it in GitHub Desktop.
This is the container with the extra volume that won't restore databases. The SQL Notebook includes the error message I'm receiving.
Display the source blob
Display the rendered blob
Raw
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
<#####################################################################################>
mkdir C:\SQLData\Docker\SQLDev64
<##>
#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\SQLDev64" #>
docker run -d -p 10064:1433 -v C:\SQLData\Docker\SQLDev64:/sqlserver -v C:\SQLData\Docker\SQLDev64:/var/opt/mssql/data/ -e ACCEPT_EULA=Y -e SA_PASSWORD=Test1234 --name testcontainer64 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\SQLDev64
<# 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. #>
$Sql64Cred = (Get-Credential sa)
#Get-SqlDatabase -ServerInstance 'localhost,10064' -Credential $Sql64Cred
#docker exec -it testcontainer64 bash
Start-Sleep -Seconds 4
<# Use the Get-SqlDatabase cmdlet to see all the databases on the instance. #>
Get-SqlDatabase -ServerInstance 'localhost,10064' -Credential $Sql64Cred
<# 3) Restore the AdventureWorks2016 database #>
Restore-SqlDatabase -ServerInstance 'localhost,10064' -Credential $Sql64Cred -BackupFile '/sqlserver/AdventureWorks2016.bak' -Database 'AdventureWorks2016' -AutoRelocateFile
<# Use the Get-SqlDatabase cmdlet to see all the databases on the instance. #>
Get-SqlDatabase -ServerInstance 'localhost,10064' -Credential $Sql64Cred
<# 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\SQLDev64\$($BakInfo.name)"
#Restore-SqlDatabase -ServerInstance 'localhost,10064' -Credential $Sql64Cred -BackupFile "/sqlserver/$($BakInfo.name)" -Database ($BakInfo.name -replace '.bak') -AutoRelocateFile
#};#
#Get-SqlDatabase -ServerInstance 'localhost,10064' -Credential $Sql64Cred
#<# Clean-up:
#docker stop testcontainer64
#docker rm testcontainer64
#dir -Path C:\SQLData\Docker\SQLDev64 -Filter AdventureWorks*.bak | Remove-Item
#>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment