DEV Community

Alan Sarli
Alan Sarli

Posted on

Restoring an SQL Server backup in a docker container

At our company, we wanted to run tests as E2E as possible, both in our local dev computers running windows and in our pipeline: GitHub action with the latest ubuntu (https://github.com/actions/virtual-environments/blob/main/images/linux/Ubuntu2004-README.md), and that includes reaching out to a real SQL Server DB so we could verify our queries and tables are working fine.

We found it super challenging to create a solution that works in both Windows and Linux, lots of online suggestions don't seem to work anymore, so took us a while to work it out, but we finally managed so we decided to share with the world.

These are all the steps we followed:

1: Use a compression method that works in both Windows and Linux

The DB backup is normally huge, and we need to store it in Github and move it around, so we want it compressed.
The command tar matches the requirement, and works in windows 10 out of the box (https://superuser.com/a/1515028):

tar -cvzf mvc_db_2021-08-25.tar.gz mvc_db.bak
Enter fullscreen mode Exit fullscreen mode

2: Put backup in a place accessible by SQL Server container

To be able to share access to it, we created a folder named 'sql_server_backup'.

image

Which is then shared via a volume in docker-compose, simplified example:

version: '3.9'
services:
  tests:
    image: my-special-web-project-with-db-backup
    depends_on:
      - db
    volumes:
      - ./sql_server_backup:/sql_server_backup

  db:
    image: mcr.microsoft.com/mssql/server:2019-latest
    volumes:
      - ./sql_server_backup:/sql_server_backup
volumes:
  sql_server_backup:
Enter fullscreen mode Exit fullscreen mode

3: Run docker compose and decompress the backup

In our repo root folder, we created a set-local-environment.sh:

#!/bin/bash -e

docker-compose -f docker-compose-infra.yml up -d

cd sql_server_backup
tar -xzvf *.tar.gz 

# docker attach allow us to the logs inside the container, VERY useful for debugging
#docker attach db_1

echo "Docker compose finished running"

Enter fullscreen mode Exit fullscreen mode

The order of the commands is quite important, we want docker compose to go first, as starting up the SQL Server instance can take a while, in the meantime we are decompressing the backup

4: Restore the backup

This step has been the most difficult, as there is a timing issue, SQL Server can take a while to be ready for use, and recommendations to use scripts like wait-for-it.sh did not work for us.
What worked was leveraging the health check mechanism, that runs regularly, if the DB is not ready to start restoring, it tries again a bit later. Here is the relevant section in the docker-compose:

version: '3.9'

services:
  db:
    image: mcr.microsoft.com/mssql/server:2019-latest
    environment:
      MSSQL_SA_PASSWORD: Password123
      ACCEPT_EULA: Y
      MSSQL_PID: Express
    healthcheck: # copied / adapted from https://github.com/Microsoft/mssql-docker/issues/133, leveraging https://docs.docker.com/engine/reference/builder/#healthcheck
      test: sh /sql_server_backup/RestoreDb.sh
      interval: 10s
      timeout: 10s
      retries: 10
      start_period: 45s
    ports:
      - "1433:1433"
    volumes:
      - ./sql_server_backup:/sql_server_backup
Enter fullscreen mode Exit fullscreen mode

RestoreDb.sh:

#!/bin/bash -e

/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "Password123" -i /sql_server_backup/RestoreDb.sql
Enter fullscreen mode Exit fullscreen mode

RestoreDb.sql (with a bit of debugging logic):

-- this file will be called from the docker compose health check

if exists ( select 1  from sys.databases  where [name] = 'MvcDocker' )
    begin
    EXEC xp_logevent 60000, 'RestoreDb.sql: MvcDocker database already exists, no need to run again', informational;
    return -- already run
    end

EXEC xp_logevent 60000, 'RestoreDb.sql: Started script', informational;

-- begin write to log the list of files in our folder of interest
declare @ss table ([filename] varchar(1000), depth int, [file] int)

insert into @ss
EXEC xp_dirtree '/sql_server_backup/', 2, 1 -- list all files in our folder of interest (https://stackoverflow.com/a/13594903)

declare @msg        varchar(2000) = 'RestoreDb.sql: Files found in /sql_server_backup/: '

select @msg = @msg + [filename] + ',' from @ss

EXEC xp_logevent 60000, @msg, informational;
-- end write to log the list of files in our folder of interest

GO
-- restore the backup, to names and folder that make sense here
RESTORE DATABASE MvcDocker FROM DISK='/sql_server_backup/mvc_db.bak'
WITH MOVE 'OriginalFileName' TO '/var/opt/mssql/data/MvcDocker.mdf',
     MOVE 'OriginalFileName_log' TO '/var/opt/mssql/data/MvcDocker_log.ldf'
GO
use MvcDocker
GO
-- check if the restored DB works as expected
select top 5 DisplayName from [SomeTableFromYourDb]

Enter fullscreen mode Exit fullscreen mode

5: Github workflow

In our case, we were using .net and only our integration tests needed the DB, so we run it last, simplified example:

jobs:
  build_and_test_and_publish:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout
        uses: actions/checkout@v2

      - name: Create folder BuildReports
        run:  mkdir BuildReports

      - name: DotNet restore
        run:  dotnet restore --verbosity m > BuildReports/Restore.txt

      - name: DotNet build
        run: dotnet build --no-restore  --verbosity m --configuration Release /nowarn:CS1591 /p:Version=$NUGET_PACKAGE_VERSION > BuildReports/Build.txt

      - name: DotNet unit tests
        run: dotnet test --no-build --configuration Release --filter=Type=Unit > BuildReports/UnitTests.txt

      - name: Docker setup
        run: sh ./set-local-environment.sh  > BuildReports/DockerSetup.txt

      - name: DotNet integration tests
        run: dotnet test --no-build --configuration Release --filter=Type=Integration > BuildReports/IntegrationTests.txt
Enter fullscreen mode Exit fullscreen mode

Debugging

Try this window on your docker, this is what you should see if every works as expected

image

You will notice the message 'database already exists' will keep showing up, that's because the docker compose health check keeps running non stop.
At first our health check command will not work, because while the container itself is 'ready', the SQL Server inside it is still loading up, so the command fails silently.
When SQL Server is ready, the next iteration of the health check command will work and the DB will be restore.
In the next health check iteration, the DB will be already restored, so we will start seeing the message 'database already exists'.

PS

We are searching for talented software engineers, please have a look at https://clientapps.jobadder.com/30580/bizcover

Discussion (0)