I'm working on Visual Studio and for the database Azure Data Studio, I need MS SQL Server since my OS is Mac I have to use Docker to create the connection. The only problem I have is when I back up the database, it's saved in a virtual folder where I cannot use it in Visual Studio. How can I find or backup my database so I can use it?
I expect to find a physical folder of my database.
Here's the tutorial SQL Server : Backup and Restore on Docker can help you copy the backup file from the running container to the host(actual machine where docker container is running).
Summary:
We will be using SQL Operations studio to run our Backup and Restore queries. To take the backup, you need to execute the following query in your query window –
BACKUP DATABASE project1 TO DISK = N’/var/opt/mssql/data/project1.bak’
GO
where DISK=”Specify the location where you want to generate the backup of your database in your container”
This query will generate the backup file(.bak) inside the container. To restore it to somewhere else, we need to first copy the backup file from the running container to the host(actual machine where docker container is running).
Going back to the terminal and running the following docker copy command –
docker cp mssqlserver:/var/opt/mssql/data/project1.bak /Users/jbond/
where,
Now I’m going to restore it to the SQL Server running on my windows machine.
Note: Since I took the backup on SQL Server version 2017, I need to restore it on the same version for compatibility.
Hope this helps.
You can try the below command
docker run --name MsSql -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=FeteBird@sql' -p 1433:1433 -v /var/opt/mssql/data:/var/opt/mssql/data -d mcr.microsoft.com/mssql/server:2019-latest
On the mac location /var/opt/mssql/data copy the backup data then browse from the azure data studio
In the Docker Desktop app, click on the container running your SQL instance
Under the files tab scroll down to the opt folder and expand opt->mssql
Right-click the "data" folder inside the "mssql" folder and click Import, select your .bak file
Your bak file will now be accessible in ADS.
