I will never, and I mean EVER, forget when I began my SQL Server journey at CBT Nuggets. The year was 2015, and I was committed to take the Microsoft SQL Server Developer certification exams (spoiler: I skipped it and went for the SQL DBA exam). And the thing that I remember most about that course was that Garth used a database of Star Wars data.
Turns out Garth made that database himself – I always thought it was something he downloaded. SO after receiving Garth’s permission, I wanted to make this database portable. Leveraging some docker container wizardry, here’s how we can turn a database backup (a .BAK file) into a running container. So if you’d like to follow along, the full repo can be found here. The actual code that we are reviewing is the swdocker.sh script.
First things first, make sure that your WSL instance is in fact running on Version 2, that Docker will use that Linux distro, and that your WSL instance has sudo privileges for Docker commands by default. Now to the code
docker pull mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04 docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=MyStrongPw!23' --name 'swsql' -p 1402:1433 -v sql1data:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04 docker exec -it swsql mkdir /var/opt/mssql/backup docker cp SWDB.bak swsql:/var/opt/mssql/backup docker exec -it swsql /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'MyStrongPw!23' -Q 'RESTORE FILELISTONLY FROM DISK = "/var/opt/mssql/backup/SWDB.bak"' | tr -s ' ' | cut -d ' ' -f 1-2 docker exec -it swsql /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'MyStrongPw!23' -Q 'RESTORE DATABASE SWDB FROM DISK = "/var/opt/mssql/backup/SWDB.bak" WITH MOVE "SWDB" TO "/var/opt/mssql/data/SWDB.mdf", MOVE "SWDB_Log" TO "/var/opt/mssql/data/SWDB_log.ldf"'
The very first line pulls (downloads) the latest version of MS SQL Server running on Ubuntu. The next command creates the container and does some important things. Notably, it opens port 1402 instead of the standard 1433 for SQL Server. Why 1402? I have no idea why I chose 1402. But I did. You can deal with it or change it if you want. It also sets the sa (system admin) password for the SQL instance, and creates a disk volume that we use to transfer the Star Wars database backup file into the container.
The next line creates a directory within the container where we will copy our Star Wars database backup file into. The following line performs that copy operation.
The last two docker exec commands run T-SQL commands within the container that actually perform a database restore from the Star Wars backup file
If all goes well, you will see output like the one above, primarily noting the last line that reads RESTORE DATABASE successfully processed 402 pages in 0.063 seconds (49.789 MB/sec).
At this point you can open MS SQL Server Management Studio (SSMS) or Azure Data Studio and connect to “localhost,1402” – worth noting the syntax there. SQL Server identifies listening ports by a comma rather than a colon like we are used to. So specify the server as “localhost,1402” , set Authentication mode to SQL Server authentication, and you can use the “sa” username with the password provided in the code above to connect.
Worth pointing out, you can also fire up some Python scripts and use the PyODBC library to query the database and convert the results to Python data objects. I have examples of this here, albeit using a different database.