Restore Database Backup to Docker SQL Server Image on macOS

Restore Database Backup to Docker SQL Server Image on macOS

In an earlier article I have explained how to install SQL Server on macOS using Docker Desktop. Now we will see how to restore a database backup to Docker SQL Server Image on macOS.

Create a data volume container while running the SQL Server

After pulling your desired version of SQL Server from Docker Hub, you have to run the container image with the below command in Terminal.app.

docker run --name mssql_docker -e 'ACCEPT_EULA=Y' \
-e 'SA_PASSWORD=[[Your-SA-Password]]' -p 1433:1433 \
-v mssql_docker_data:/var/opt/mssql \
-d mcr.microsoft.com/mssql/server:2019-latest
NOTE:
  • Change [[Your-SA-Password]] to a strong password with at least 8 characters. It should include three of these four categories: lowercase letters, uppercase letters, non-alphanumeric symbols and numbers.
  • mssql_docker is the name I have given to the SQL Server. You can name it as you wish.
  • The optional parameter value -v mssql_docker_data:/var/opt/mssql creates a data volume container. The name I have given to the data volume is mssql_docker_data.
  • This command will run the SQL server in Developer edition by default. If you want to run the SQL Server in other editions, then add the environment variable MSSQL_PID to the command with the values like Express, Standard, Enterprise, EnterpriseCore or Developer.

To check and confirm if the SQL Server is running, execute the below command in Terminal.

docker ps
Check if SQL Server docker container image is running

To check the volumes created, use the volume ls command.

docker volume ls
check the volumes created in Docker SQL Server Container

Copy the database backup into the container

Before copying the backup file, make sure to create a folder called backup. using the mkdir command as given below.

docker exec -it mssql_docker mkdir /var/opt/mssql/backup

Now, using the cp command copy the backup file from your local folder to the backup folder on the container. Here the backup file is Test.MyTecBits.com.bak. and it is in my Downloads folder.

docker cp /Users/BeaulinTwinkle/Downloads/Test.MyTecBits.com.bak \
mssql_docker:/var/opt/mssql/backup
copy backup from local folder to the container

Restore the database from the backup

Once the backup is available inside the container, you can restore it. However, it is good to get the logical and physical names of the data, log and other files from the backup using the RESTORE FILELISTONLY command. This will help you in creating the RESTORE DATABASE command.

docker exec -it mssql_docker /opt/mssql-tools/bin/sqlcmd \
-S localhost -U SA -P '[[Your-SA-Password]]' \
-Q 'RESTORE FILELISTONLY FROM DISK = "/var/opt/mssql/backup/Test.MyTecBits.com.bak"' \
| tr -s ' ' | cut -d ' ' -f 1-2

The result of RESTORE FILELISTONLY command will be similar to this:

LogicalName PhysicalName
-------------------------------------------------------
Test.MyTecBits.com C:\Program\Data\Test.MyTecBits.com.mdf
Test.MyTecBits.com_log C:\Program\Data\Test.MyTecBits.com_log.ldf

(2 rows

Based on the above listed logical names, create a RESTORE DATABASE command. In my case there is one data file and one log file. So the comment will be as below.

docker exec -it mssql_docker /opt/mssql-tools/bin/sqlcmd \
-S localhost -U SA -P '[[Your-SA-Password]]' \
-Q 'RESTORE DATABASE [Test.MyTecBits.com] FROM DISK = "/var/opt/mssql/backup/Test.MyTecBits.com.bak" WITH MOVE "Test.MyTecBits.com" TO "/var/opt/mssql/data/Test.MyTecBits.com.mdf", MOVE "Test.MyTecBits.com_log" TO "/var/opt/mssql/data/Test.MyTecBits.com_log.ldf"'

NOTE: If your database has .ndf or InMemory files, then create the RESTORE statement accordingly.

The success message will be like this:

Processed 608 pages for database 'Test.MyTecBits.com', file 'Test.MyTecBits.com' on file 1.
Processed 1 pages for database 'Test.MyTecBits.com', file 'Test.MyTecBits.com_log' on file 1.
RESTORE DATABASE successfully processed 609 pages in 0.499 seconds (9.521 MB/sec).

Verify the restored database

The restoration of database backup to docker SQL Server container image is now complete. To verify that, you can execute a select query against the sys.databases through terminal.

docker exec -it mssql_docker /opt/mssql-tools/bin/sqlcmd \
-S localhost -U SA -P '[[Your-SA-Password]]' \
-Q 'SELECT name, create_date, state_desc FROM sys.databases'

In the result you can see the restored database name, created dateline and the current state of the database.

name                     create_date             state_desc 
------------------------ ----------------------- -----------
master                   2003-04-08 09:13:36.390 ONLINE     
tempdb                   2020-07-10 10:03:35.977 ONLINE     
model                    2003-04-08 09:13:36.390 ONLINE     
msdb                     2020-06-10 19:01:05.513 ONLINE     
WideWorldImporters       2020-07-09 10:56:17.463 ONLINE     
Test.MyTecBits.com       2020-07-10 16:27:45.383 ONLINE     

(6 rows affected)

Now you can use a client tool likeĀ Azure Data Studio to connect the restored database on the SQL Server container image and work on it.

Related articles


Leave your thoughts...

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