Do you have experience handling project without dedicated development environment? This was actually my first time, There is no dedicated development environment at all. I only can access the webapp for make sure the function is properly.
It became quite challenging when problems occured. I often needed extra clarification and some issues were not easy to reproduce. One of those problems happened recently, and I want to share how I solved it.
Background
During my time developing DMS (Document Management System), I use mssql inside a docker container. My development workflows was simple ... only one command to run application. The shortcut command will be:
- starting mssql service
- stating sqlpad
- running application
I love using docker because it gives me an isolated environment, a clean setup and fast development experience.
However, my frontend teammate preferred running everything natively on his machine. Because of that, sometimes our environments didn't behave the same.
One day, he told me "I think there's bug in our application, I can't create meeting schedule". I checked the feature on my machine and on the dev server, everything worked perfectly.
After a while, he sent me database backup file with bak extension from his local machine. I need to restore that database to my dockerized mssql so I could debug the issue.
Try Hard
I started searching the internet for ways to restore the database. Many posts suggested using the sqlcmd tool installed directly on the host machine, like this:
sqlcmd -S [server_name] -U [username] -P [password] -Q "RESTORE DATABASE [database_name] FROM DISK='C:\Path\To\Your\backup_file.bak' WITH RECOVERY"
I found another solution, I didn't need to install sqlcmd tool on my host
machine. These steps will be:
- copy the backup file to docker container
- change permission of the file
- restore database
Copy file to container
Docker has the docker cp tool to copy file to container. Example command:
docker cp path/to/file/container:/path/to/file/host
Based on what I read, sqlcmd looks for files in /var/opt/mssql/data directory. So, I
need to copy file to /var/opt/mssql/data directory. Example command:
docker cp EDocument.bak digital_doc_mssql:/var/opt/mssql/data/EDocument.bak
Change permission
After copying file, I needed to change the file permissions. This is needed cause the
docker image run as mssql user. This docker image I used mcr.microsoft.com/mssql/server:2022-latest.
To change permission, I use docker exec command and following chown command. Example command:
docker exec -u 0 container_id bash -c "chown mssql path/to/file"
so, change path file to /var/opt/mssql/data/EDocument.bak:
docker exec -u 0 digital_doc_mssql bash -c "chown mssql /var/opt/mssql/data/EDocument.bak"
Restore database
I use volume for my database container, so I don't worry to directly change data on container. Even though I restart the container, the data will not be lost.
To restore database, I need to enter the container first. Use docker exec command
and following with bash shell type:
docker exec -it container_id bash
change container_id to your container id. Example command:
docker exec -it digital_doc_mssql bash
Usually, in container mssql sqlcmd command not directly available in the PATH. So,
we need to know the path of the sqlcmd location. Using instinct as developer I found location of sqlcmd is /opt/mssql-tools18/bin/sqlcmd.
Then, I use sqlcmd command to restore database. Example command:
/opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -C
That command will ask for password and then typing these command each line:
1> RESTORE DATABASE [EDocument]
2> FROM DISK = N'/var/opt/mssql/data/EDocument.bak' WITH
3> MOVE 'EDocument' TO N'/var/opt/mssql/data/EDocument.mdf',
4> MOVE 'EDocument_log' TO N'/var/opt/mssql/data/EDocument_log.ldf',
5> REPLACE
6> GO
After run this command, the database will be restored. You will get message like:
Processed 2400 pages for database 'EDocument', file 'EDocument' on file 1.
Processed 1 pages for database 'EDocument', file 'EDocument_log' on file 1.
RESTORE DATABASE successfully processed 2401 pages in 0.034 seconds (551.513 MB/sec)
Conclusion
Instead of installing extra tools on my host machine, I used the tools already inside the Docker container. I like maximizing what’s already available before bringing in new dependencies. Of course, this approach may be different if you're running SQL Server directly on your host.
I hope this article can help you. If you have any question, please comment below. Thanks for reading.