Setting Up SQL Server Express And Restore DB From Backup File On Mac

Did you know you cannot directly install SQL Server Express on Mac? I was actually shocked because it is popular and I expected I could just install it on Mac. But no, that wasn't the case. I had to go through several hoops to get it working on a mac and sharing it here so that you don't have to.

Docker To The Rescue

You can install SQL Server on mac only via Docker. So installing Docker and making sure the Docker engine is running on your laptop is the first requirement. Assuming you have Docker installed, these are steps you need to follow:

1. Pull the SQL Server image

docker pull microsoft/mssql-server-linux

2. Run the docker container

docker run -d --name sqlserver -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=myPassw0rd' -p 1433:1433 microsoft/mssql-server-linux

--name
Name of the container -p 1433:1433 Local port maps to port on the container

3. Check if the container is running

docker ps -all

If its running, you would see something like this:

CONTAINER ID        IMAGE                          COMMAND                  CREATED             STATUS              PORTS                    NAMES
740fcb5193c6        microsoft/mssql-server-linux   "/bin/sh -c /opt/m..."   33 seconds ago      Up 31 seconds       0.0.0.0:1433->1433/tcp   sqlserver

Voila! You have SQL Server Express running on your mac. However, like in most cases, you may have to restore a backup file (.bak). Let's see how to copy the backup file into the container and restore the DB in the following steps.

4. Log into the container and create a folder for backup

docker exec -it sqlserver "bash"
mkdir /var/opt/mssql/backup

docker exec command will log you into the container and you can run commands inside.

5. Copy a file from the host to the container

docker cp backupfileonhost.bak sqlserver:/var/opt/mssql/backup

6. Get a list of files in the backup

```docker exec -it sqlserver /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'Password@1234' -Q 'RESTORE FILELISTONLY FROM DISK = "/var/opt/mssql/backup/.bak"' | tr -s ' ' | cut -d ' ' -f 1-2

**7. Restore from the backup 
**

```docker exec -it sqlserver /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'Password@1234' -Q 'RESTORE DATABASE MT_Primary FROM DISK = "/var/opt/mssql/backup/temp.bak" WITH MOVE "Databasename" TO "/var/opt/mssql/data/temp.mdf", MOVE "Temp_log" TO "/var/opt/mssql/data/temp_log.ldf"'

That's all folks! You have your SQL Server running with the data restored from a backup file.

Let me know if you run into any issues in the comments.