Copy MDF and LDF files to local machine
I wanted to copy a SQL Server db with about 60 tables to a docker image, mainly for testing purposes. It was really straightforward and has likely been covered elsewhere but I just wanted to document it.
This assumes that you have the following:
- SQL Server and SQL Server Management Studio running in a Windows environment
- A docker image of SQL Server
First of all, you want to copy the mdf and ldf files to your local machine. Do this by right clicking the database that you wish to copy in Sql Server management studio. Select ‘Properties’ -> Files. The Path column will show you where the files will be saved to. It will be something like C:\Users\JohnA
Click OK and check that your files were saved to the folder specified. The file types should be SQL Server Primary Database File (for the mdf file) and SQL Server Database Transaction Log File (for the ldf file).
Copy from local machine to Docker instance
Find the name of your docker container by opening a command line and entering:
The name will be in the last column, called NAMES. Let’s say it is called BassGuy1234 for now. Use the command:
docker cp C:\Users\JohnA BassGuy1234:\var\opt\mssql\data
Connect to the Docker container’s instance in Sql Server Management studio and right click on Databases. Select ‘Attach’. You should see a table called ‘Databases to Attach:’. Select Add.. and traverse the file explorer to find your mdf file in \var\opt\mssql\data. Select OK, and that should be you.