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
To check the volumes created, use the volume ls command.
docker volume ls
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
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.