Restore A SQL Server Backup Using Azure Data Studio Tool On MacOS

This article is focused on a recent requirement in which SQL Server needed to be accessed locally on a MacOS machine to analyse the database and its structure. 

SQL Server is usually managed using SQL Server Management Studio. However, this tool is not available for MacOS. For recent editions, Microsoft provides a tool called Azure Data Studio that can help MacOS users take advantage of SQL Server. You can get a backup of your SQL Server database and can restore it using the Data Studio tool on your MacOS. 

Here are the steps to restore a SQL Server backup file on your MacOS using Docker containers. 

Prerequisites

1. Docker should be installed.
2. Azure Data Studio for MacOS should be installed.
3. You’ll need the SQL Server database backup file (.bak).
    

Note: We will be installing SQL Server as a Docker image.

Procedure

Pull the latest SQL Server image using the Docker CLI.

docker pull mcr.microsoft.com/mssql/server:2017-latest

Run the Docker image by passing the sa user’s password (Welcome123 in this case). 

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Welcome123" -p 1433:1433 --name sql1 -d mcr.microsoft.com/mssql/server:2017-latest

To check if the container is up or not:

docker ps -a 

We will now be logging into the container. Successful login will give you the root prompt.

docker exec -it sql1 "bash" 

The command below is to log in into the SQL Server instance in the Docker container.

 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "Welcome123"

Now type the following commands (one per line). We are creating a test database here.

1> CREATE DATABASE TestDB

2> SELECT Name from sys.Databases

3> GO

Quit the SQL prompt by typing exit.

We will be using the LOREAL_FINANCEv15.bak as the SQL backup file name in the following steps as an example. You should replace it with your respective file name.

Exit the container and give the following command in your terminal window. We are using the command to move the backup file into the SQL Server container for accessing through Azure Data Studio.

docker cp LOREAL_FINANCEv15.bak sql1:/var/opt/mssql/data/LOREAL_FINANCEv15.bak

The steps below are to be executed on Azure Data Studio, installed on your MacOS.

Open Azure Data Studio and click on New connection. Enter the details as below. Provide the password for the sa user that you have given in the command line above.

If all goes well, you will now be connected as per the steps given above.

Expand Databases, right click on Test DB and select restore. You will get a window such as the one shown below:

Click on Restore from and select Backup file.

In the Backup file path field that comes up as soon as you select the above option, select the three dots to enter the Select a file screen. At the bottom, type /var/opt/mssql/data if its not already present, and expand the Data option in the part of the window above. You will now see the backup file. Select it and click ok.

The Detected DB information will be displayed (see below). Click on Restore.

Once the database is restored, you will be able to see the details of the database (see screenshot below).

You can now open the query window and access your data in the database.