Restoring an SQL Server backup in a docker container
Tags: docker, devops
Published: 2021-09-22 00:28:54

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):

```bash
tar -cvzf mvc_db_2021-08-25.tar.gz mvc_db.bak
```

# 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jj7ayyja7pk6giug5riw.png) 

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

```yaml
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:
```

#3: Run docker compose and decompress the backup
In our repo root folder, we created a set-local-environment.sh:

```bash
#!/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"

```

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:

```yaml
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
```
RestoreDb.sh:
```bash
#!/bin/bash -e

/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "Password123" -i /sql_server_backup/RestoreDb.sql
```
RestoreDb.sql (with a bit of debugging logic):
```sql
-- 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]

```

#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:

```yaml
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
```
#Debugging
Try this window on your docker, this is what you should see if every works as expected

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/38ba7sub56vazo653k79.png)

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