CODEWITHSUNDEEP
dockerdocker-composedockerfile
Joshua Abbott
Joshua Abbott

Reputation: 442

Restore a SQL Server DB.bak in a Dockerfile

I am running a .NET Razor application, an instance of gitea, and a SQL Server database each in separate containers that communicate with one another. I would like to start my database image with a database schema and data (by restoring a .bak file).

I can do this with my current Dockerfile, if once it is up and running, I run these additional commands:

  1. docker exec -it myContainer /opt/mssql-tools/bin/sqlsmd -S localhost -U sa -P myPassword

  2. /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P myPassword -Q "RESTORE DATABASE MY_DB_NAME FROM DISK='/var/opt/mssql/backup/MY_DB_NAME.bak' WITH MOVE 'MY_DB_NAME_TEST' TO '/var/opt/mssql/data/MY_DB_NAME_TEST.mdf', MOVE 'MY_DB_NAME_TEST_log' TO '/var/opt/mssql/data/MY_DB_NAME_TEST_log.ldf'"

This gets the job done, but I want to fully automate the process so that this is configured 100% by my docker-compose.yml and Dockerfile so I need only type: docker-compose up -d.

I don't think the content of my docker-compose.yml file is relevant, but here is my Dockerfile (where I am trying to run that script that I currently need to run after docker-compose up):

FROM microsoft/mssql-server-linux

ENV SA_PASSWORD=myPassword
ENV ACCEPT_EULA=Y

COPY ./ACES_DB.bak /var/opt/mssql/backup/MY_DB_NAME.bak
RUN docker exec -it myContainer bin/sh /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P myPassword -Q "RESTORE DATABASE MY_DB_NAME FROM DISK='/var/opt/mssql/backup/MY_DB_NAME.bak' WITH MOVE 'MY_DB_NAME_TEST' TO '/var/opt/mssql/data/MY_DB_NAME_TEST.mdf', MOVE 'MY_DB_NAME_TEST_log' TO '/var/opt/mssql/data/MY_DB_NAME_TEST_log.ldf'"

Any help would be much appreciated.

Upvotes: 6

Views: 21772

Answers (5)

Schmorrison
Schmorrison

Reputation: 194

Expanding on @joshua-abbott 's answer. Here is my setup for restoring multiple DB to mssql 2019 docker image, and replacing the 'default' password used to restore the DB.

Dockerfile

FROM mcr.microsoft.com/mssql/server:2019-latest
ENV DEFAULT_MSSQL_SA_PASSWORD=myStrongDefaultPassword # build password
ENV ACCEPT_EULA=Y
USER root

COPY restore-db.sh entrypoint.sh /opt/mssql/bin/
RUN chmod +x /opt/mssql/bin/restore-db.sh /opt/mssql/bin/entrypoint.sh

ADD data.tar.gz /var/opt/mssql/
RUN chown -R mssql:root /var/opt/mssql/data && \
                chmod 0755 /var/opt/mssql/data && \
                chmod -R 0650 /var/opt/mssql/data/*

USER mssql
RUN /opt/mssql/bin/restore-db.sh
CMD [ "/opt/mssql/bin/sqlservr" ]
ENTRYPOINT [ "/opt/mssql/bin/entrypoint.sh" ]

restore-db.sh

#!/bin/bash

export MSSQL_SA_PASSWORD=$DEFAULT_MSSQL_SA_PASSWORD

# start the MSSQL server with the build password
(/opt/mssql/bin/sqlservr --accept-eula & ) | grep -q "Server is listening on" && sleep 2

for restoreFile in /var/opt/mssql/data/*.bak
do
    fileName=${restoreFile##*/}
    base=${fileName%.bak}
    # restore the files to the db
    /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P $MSSQL_SA_PASSWORD -Q "RESTORE DATABASE [$base] FROM DISK = '$restoreFile'"
    rm -rf $restoreFile
done

entrypoint.sh

#!/bin/bash

# replace the password used to build the container with one that is in ENV
/opt/mssql-tools/bin/sqlcmd \
    -l 60 \
    -S localhost -U SA -P "$DEFAULT_MSSQL_SA_PASSWORD" \
    -Q "ALTER LOGIN SA WITH PASSWORD='${MSSQL_SA_PASSWORD}'" &

# start the MSSQL server, $@ is expanded to the CMD from the dockerfile
# effectively ~$: /opt/mssql/bin/permissions_check.sh "/opt/mssql/bin/sqlservr"
/opt/mssql/bin/permissions_check.sh "$@"

Upvotes: 6

JEuvin
JEuvin

Reputation: 1041

One thing Joshua forgot to add was the sql script:

Dockerfile

FROM mcr.microsoft.com/mssql/server:2019-latest
ENV MSSQL_SA_PASSWORD=MyPassword1!
ENV ACCEPT_EULA=Y

COPY MyDatabase.bak /var/opt/mssql/data/

COPY restore-database.sql /var/opt/mssql/data/

RUN /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'MyPassword1!' -Q "EXECUTE master.sys.sp_MSforeachdb 'IF (''?'' != ''master'' AND ''?'' != ''tempdb'' AND ''?'' != ''model'' AND ''?'' != ''msdb'' ) BEGIN EXEC (''?'' .dbo.sp_changedbowner ''''sa'''') END'"
CMD /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'MyPassword1!' -d master -i /var/opt/mssql/data/restore-database.sql

restore-database.sql

RESTORE DATABBASE MyDatabase
FROM DISK = '/var/opt/mssql/data/MyDatabase.bak'
WITH MOVE 'MyDatabase' TO '/var/opt/mssql/data/MyDatabase.mdf'
MOVE 'MyDatabase_log' TO '/var/opt/mssql/data/MyDatabase_log.ldf'

Upvotes: 2

Mahmoud Nasr
Mahmoud Nasr

Reputation: 642

I voted for the answer of @Joshua Abbott , but I needed to customize the answer to match the question i.e. to restore from .bak file as it was required:

FROM mcr.microsoft.com/mssql/server:2017-latest
 
        ENV ACCEPT_EULA=Y
        ENV SA_PASSWORD=xxxxxxxx
        ENV MSSQL_PID=Developer
        ENV MSSQL_TCP_PORT=1433 
        
        WORKDIR /src
        
        COPY ["API/db/db.bak", "dbbackups/"]

        RUN (/opt/mssql/bin/sqlservr --accept-eula & ) | grep -q "Starting database restore" && /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'xxxxxxxx' -Q "RESTORE FILELISTONLY FROM DISK='/dbbackups/db.bak';"

just you need to change xxxxxxx with your password, you can name your container as you want using the docker compose file/override files

Upvotes: 2

Ruben
Ruben

Reputation: 149

It is simple, I use SQL Server Management Studio, when you create your DOCKER you declare a var for the directory, just put de Backup there and then you just restore it on your SQL

enter image description here

enter image description here

You can create a stored procedure in one of your databases for creating an automatic backup, I found this an made some adaptations for my use.

------ If you create this and then execute it------

CREATE PROCEDURE [dbo].[P_M_Backup]
AS
    DECLARE @name VARCHAR(50) -- database name  
    DECLARE @path VARCHAR(256) -- path for backup files  
    DECLARE @fileName VARCHAR(256) -- filename for backup  
    DECLARE @fileDate VARCHAR(20) -- used for file name

    -- specify database backup directory
    SET @path = '/var/opt/mssql/data/Backup/'  
     
    -- specify filename format
    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
     
    DECLARE db_cursor CURSOR READ_ONLY FOR  
        SELECT name 
        FROM master.sys.databases 
        WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'Eikon_CDEEE')  -- exclude these databases
          AND state = 0 -- database is online
          AND is_in_standby = 0 -- database is not read only for log shipping
     
    OPEN db_cursor   
    FETCH NEXT FROM db_cursor INTO @name   
     
    WHILE @@FETCH_STATUS = 0   
    BEGIN   
        SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  

        BACKUP DATABASE @name TO DISK = @fileName  
     
        FETCH NEXT FROM db_cursor INTO @name   
    END   
    
    CLOSE db_cursor   
    DEALLOCATE db_cursor
    
    /** SET @path = '/var/opt/mssql/data/Backup/'   the mssql/data/ is my directory where I have mounted the SQL Server from Docker, and Backup is a directory inside this directory, so you have to change it for your directory**/

Upvotes: 0

Joshua Abbott
Joshua Abbott

Reputation: 442

A friend and I puzzled through this together and eventually found this solution. Here's what the docker file looks like:

FROM microsoft/mssql-server-linux
ENV MSSQL_SA_PASSWORD=myPassword
ENV ACCEPT_EULA=Y

COPY ./My_DB.bak /var/opt/mssql/backup/My_DB.bak
COPY restore.sql restore.sql
RUN (/opt/mssql/bin/sqlservr --accept-eula & ) | grep -q "Starting database restore" && /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'myPassword' -d master -i restore.sql

*Note that I moved the SQL restore statement to a .sql file.

Upvotes: 11

Related Questions