How to automate creating and running a "clean" SQL Server database starting from a backup using Docker and Docker Compose?

Question

I'm onboarding in a company that handles bootstrapping the database using a small SQL Server backup file. I prefer to avoid having to pollute my main Windows installation with various middleware, so I'd like to dockerize as much of this as possible.

That said, I'm not very familiar with SQL Server administration, so I'm somewhat at a loss as to how to accomplish the details, and if my thinking on this is at all correct.

I'm considering two basic approaches to this:

• Make initializing the database (i.e. restoring the backup) part of the build for the database image. That is, I'd add a Dockerfile with FROM microsoft/mssql-server-windows-express to the project, restore the backup file, end up with a container image with the database ready as the end result.
  • The upside here is that it kind of makes sense for this to be part of the image build - if the initial backup file is updated, I only need to use docker-compose up --build to get a correct state.
  • The drawback is the data files should probably be in a Docker volume, and those don't really exist at container build-time. Having to remember to clear the volume before image rebuild to actually recreate a schema seems like it would kind of obviate the desired advantage.
• Make a one-off tool to restore the database into a MDF+LDF stored in a Docker volume, then detach them from the server. Then use the attach_dbs environment variable to attach them in the SQL Server service that'll be running long-term.
  • This approach makes it obvious that the lifetime of the database files is independent from the lifetime of any given SQL Server instance.

---

My questions then are:

1. Which of those approaches is a better idea, if they're even both at all workable?
2. Is there a better approach to accomplish going from .bak -> working database in container?
3. How do I restore, using the command-line, a SQL Server database backup to a specific path - i.e. "C:\Data" within the container. (That will be mapped to a host directory using a volume.)

Answer 1

Its not clear exactly when you need the state of the container database to be reset, both your options sound like they'd work.

In the event that changes to the backup require the database to be rebuilt, this can be done quite efficiently in a two stage windows container:

from microsoft/mssql-server-windows-developer as db_restore
copy db.bak \.

run Invoke-Sqlcmd -Query \"restore database [temp] from disk = 'c:\\db.bak' \
  with move 'Db_Data' to 'c:\\db.mdf', \
  move 'Db_Log' to 'c:\\db.ldf'\"

run Invoke-Sqlcmd -Query \"shutdown with nowait\"

from microsoft/mssql-server-windows-developer
workdir \data
copy --from=db_restore \db.mdf .
copy --from=db_restore \db.ldf .

run Invoke-Sqlcmd -Query \"create database [Db] \
  on primary ( name = N'Db_Data', filename = N'c:\\data\\db.mdf') \
  log on (name = N'Db_Log', filename = N'c:\\data\\db.ldf') for attach\"