Restoring MSSQL backup file on MacOS and querying data

MSSQL commands on Azure Data Studio

To read a .bak file from a MS SQL Server database on MacOS, one has to run MS SQL server using Docker and then use Azure Data Studio to access / query the database after restoring it.

Installing Docker image

You need to have Docker installed. Docker Desktop for MacOS is available at this link.

Once installed run the following commands.

# Pull latest image
sudo docker pull mcr.microsoft.com/mssql/server:2022-latest
# Start a container and set admin password
docker run -d --name SQL_Server_Docker -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=SuperSecretPassword' -p 1433:1433 mcr.microsoft.com/mssql/server:2022-latest

Copy the .bak file to Docker instance

docker cp DBNAME.bak SQL_Server_Docker:/tmp

Next install Azure Data Studio available at this link. Install instructions are available at this link

Next run the Azure Data Studio app and create a new connection using -

Server - localhost
User Name - sa
Password - SuperSecretPassword

Then Chose File -> New Query from the menu. Run the commands below.

Retrieve file names in .bak file

RESTORE FILELISTONLY 
FROM DISK = N'/tmp/DBNAME.bak' ;                            
GO

Restore DB by using file names from step above

RESTORE DATABASE dbname
FROM DISK = '/path/to/DBNAME.bak'
WITH MOVE 'DBNAME' TO '/var/opt/mssql/data/dbname.mdf',
MOVE 'DBNAME_log' TO '/var/opt/mssql/data/dbname.ldf' ;
GO

List tables and views in the database

USE dbname;
SELECT table_catalog [database], table_schema [schema], table_name [name], table_type [type]
FROM INFORMATION_SCHEMA.TABLES
GO

Describe table

exec sp_columns MyTableName ;
GO