setting up docker with sql server backup on mac

Install Docker Community Edition for Mac (free)

https://hub.docker.com/editions/community/docker-ce-desktop-mac?tab=description

Launch Docker

Docker will need privileged access so grant it when prompted

SQL Server may need more RAM and you can set that in Docker preferences. I found it runs fine on a mac with 4GB of RAM and the default settings in Docker which is 2GB.

Open Terminal and run
docker pull microsoft/mssql-server-linux

Launch the Docker image

sudo docker run -e “ACCEPT_EULA=Y” -e “SA_PASSWORD=Putstrongpassw0rdhere!” -p 1433:1433 –name sqlserver -v /Users/sqlvolume:/var/opt/mssql microsoft/mssql-server-linux

Optionally, you can check the Docker container with this script
docker ps

Install sql-cli – you will need to install node.js (which automatically installed npm) before running this script – https://nodejs.org/en/
npm install -g sql-cli

If you get permissions error, run it with sudo instead
sudo npm install -g sql-cli

Once sql-cli is installed, connect to sql server
mssql -u sa -p Putstrongpassw0rdhere!

When you see this, then you’ve connected successfully
Connecting to localhost…done

Run quick script to make sure sql server is working
select @@version

You should see the version info after running the previous script

To quit the sql-cli use
mssql> .quit

Get a backup of a sql server db and place it in your home dir (this is the dir that has desktop and downloads folders)

Copy it into your Docker container folder – use sudo if required – Docker has it’s own file system and can’t access your mac’s folders directly, this is why you need to copy the bak file up into the docker file system.
Docker cp YourDatabase.bak sql_server_instance:var/opt/mssql/data/
or
sudo docker cp YourDatabase.bak sql_server_instance:/var/opt/mssql/data

To see a list of files in a docker container use:
docker exec sql_server_instance ls
and to see a specific file path
docker exec sql_server_instance ls /var/opt/mssql/backup
and to search for a specific file
docker export sql_server_instance | tar tf – | grep filename

Connect to sql-cli again
mssql -u sa -p Putstrongpassw0rdhere!

Then run
RESTORE FILELISTONLY FROM DISK = N’YourDatabase.bak’;

The previous script will show you the file name of the bak. You need to use these in the restore.
You will run this script to restore the db
RESTORE DATABASE YourDatabase FROM DISK = N’YourDatabase.bak’ WITH REPLACE, MOVE ‘YourDatabase’ TO ‘/var/opt/mssql/data/YourDatabase.mdf’, MOVE ‘YourDatabase_log’ TO ‘/var/opt/mssql/data/YourDatabase_log.ldf’;

I wanted to have an interface instead of always using sql-cli, so I installed Dbeaver. You can download it from here:
https://dbeaver.io/

Make sure to download and install the Mac OS X (pkg installer + JRE)

Once you have Dbeaver installed setup a connection to localhost which is your Docker container, like below

Once you connect to it you will see the system dbs and the db you just restored into Docker.

I also setup Portainer for easier Docker container management
Run this in terminal
sudo docker run -d -p 9000:9000 portainer/portainer

Run this to see what containers are running:
docker ps

If Portainer isn’t running, use this to run it:
You may need to run this to have it connect correctly:
docker run -d -p 9000:9000 -v /var/run/docker.sock:/var/run/docker.sock portainer/portainer

After that command runs, open up a browser and point it to http://SERVER_IP:9000 where serverip is your server’s ip. In this case, I used localhost:9000 since I am running all this locally.

Setup an admin username and password

Then I got a docker daemon problem when trying to connect to local docker inside portainer

so i ran docker ps -a
to see all containers started and stopped

and discovered I had to portainer containers one started and one stopped

so I stopped the one running that gave me an error in the portainer browser interface
docker stop 8a2544e793f2
then started the other portainer container
docker start 19a5c2a3fe20

then connected in the portainer interface again – had to re-setup the admin u/p then connected to local docker successfully

not sure how that oddity happened with portainer containers

then I removed the portainer container that I stopped that didn’t work

docker rm 8a2544e793f2



Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.