1. Installing SQLCMD on CentOS 7

To install SQLCMD on CentOS 7, follow these steps:

Step 1.1: Register the Microsoft repository
Add the Microsoft repository to your system by executing the following commands:

Code Block
sudo curl -o /etc/yum.repos.d/mssql-release.repo https://packages.microsoft.com/config/rhel/7/prod.repo

Step 1.2: Install the mssql-tools package
Install the mssql-tools package along with the UnixODBC development package:

Code Block
sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts sudo yum install mssql-tools unixODBC-devel

Step 1.3: Add the tools to your PATH
Add the sqlcmd and bcp tools to your PATH:

Code Block
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile source ~/.bash_profile

Now, you should be able to use the sqlcmd utility. Check with sqlcmd -?

2. Backing Up the Master Database

To back up the master database on localhost, follow these steps:

Step 2.1: Connect to SQL Server
To connect to the SQL Server instance on localhost, use the sqlcmd utility. Replace <username> and <password> with the appropriate login credentials:

Code Block
sqlcmd -S localhost -U <username> -P <password>

Step 2.2: Backup the master database
Once connected, execute the following T-SQL script to back up the master database. Replace <backup_file_path> with the desired path to store the backup file:

Code Block
BACKUP DATABASE master TO DISK = N'<backup_file_path>' WITH FORMAT, NAME = N'master-Full Database Backup'; GO

For example:

Code Block
BACKUP DATABASE master TO DISK = N'/var/opt/mssql/backup/master.bak' WITH FORMAT, NAME = N'master-Full Database Backup'; GO

Type EXIT to quit the SQLCMD utility.

3. Extracting the backup file from the docker container

To extract the backup file from the docker container where it was created, use one of these approaches

Approach 1

Using the docker cp sub-command.

First, use docker ps to get the name of the mssql container.

...

Table of Contents

minLevel	1
maxLevel	7
outline	true

1. Backing up

1. Stop the containers

First and foremost you’ll need to stop the app and the database:

Code Block
$ docker compose down

You should see a message similar to

Code Block
[+] Running 3/3 ⠿ Container lakehouse Removed 1.1s ⠿ Container bplm Removed 0.4s ⠿ Network centos_bplm_net Removed

This tells you that the Lakehouse monitor and the DB are down.

2. Locate the MSSQL data

Now you will need to identify the path on the host system where your MSSQL data resides. Do this by using the following approach:

Code Block

$ sudo -i
# docker volume ls
DRIVER    VOLUME NAME
local     centos_mssql_data
local     centos_spring_session
# docker volume inspect centos_mssql_data
[
    {
        "CreatedAt": "2023-03-15T07:27:39Z",
        "Driver": "local",
        "Labels": {
            "com.docker.compose.project": "centos",
            "com.docker.compose.version": "2.16.0",
            "com.docker.compose.volume": "mssql_data"
        },
        "Mountpoint": "/var/lib/docker/volumes/centos_mssql_data/_data",
        "Name": "centos_mssql_data",
        "Options": null,
        "Scope": "local"
    }
]
# cd /var/lib/docker/volumes/centos_mssql_data/_data
# ls -la
total 8
drwxrwx---. 7 root  root   73 Apr  5 12:36 .
drwx-----x. 3 root  root   19 Mar 15 07:27 ..
drwxr-xr-x. 5 10001 root   74 Mar 15 07:27 .system
drwxr-xr-x. 2 10001 root   24 Apr  5 12:36 backup
drwxr-xr-x. 2 10001 root 4096 Mar 15 07:27 data
drwxr-xr-x. 2 10001 root 4096 Apr  6 10:10 log
drwxr-xr-x. 2 10001 root   25 Mar 15 07:27 secrets

3. Archive the data

Once in this step, you can create a backup archive of this entire directory using the tar command

Code Block
# tar zcvf ~centos/mssql-backup.tgz . ./ ./.system/ ./.system/system/ ./.system/system/Temp/ ./.system/system/lsasetup.log ....

Note: in my script above I have saved the tgz file in the centos home folder, as such: ~centos/mssql-backup.tgz

Now you need to change the owner of the file so that your user, in my case centos can access the file.

Code Block
# cd ~centos # chown centos mssql-backup.tgz

4. Start the containers

You can now leave the privileged session using the exit command and restart the Lakehouse containers

Code Block
# exit $ docker compose up -d # make sure you're in the same directory where your docker-compose.yml file is located

The output should look similar to this:

Code Block
[+] Running 3/3 ⠿ Network centos_bplm_net Created 0.1s ⠿ Container bplm Started 0.9s ⠿ Container lakehouse Started

You can check that the containers are up and running with the following command:

Code Block

$ docker ps
CONTAINER ID   IMAGE                                                      COMMAND                  CREATED         STATUS         PORTS                                                                                        NAMES
ec90f0e0c174240a0312e897   blueprint.azurecr.io/bpcs/lakehouse-optimizer:220230406.31   "./start-app.sh"         284 hoursseconds ago   Up 283 hoursseconds   2222/tcp, 0.0.0.0:4040->4040/tcp, :::4040->4040/tcp, 0.0.0.0:80->8080/tcp, :::80->8080/tcp   bplm
2b07a8da8b820287182a2d92   mcr.microsoft.com/mssql/server:2019-latest                 "/opt/mssql/bin/perm…"   34 weeksseconds ago    Up 473 hoursseconds   0.0.0.0:1433->1433/tcp, :::1433->1433/tcp                                                    lakehouse

From the above output we know that the mssql server name is lakehouse (rightmost column in the output).

...

5. Use `scp` to download the data

You can use scp to copy the backup file to the host system.

Code Block
docker cp lakehouse:/var/opt/mssql/backup/master.bak ~/master.bak

Approach 2

The bak file is created in the docker container, but the data is accessible from the host system. You just need to know where docker stores your mssql volume. Use the following commands:

Code Block
docker volume ls # to list your volumes docker volume inspect <volume name> # to find the mountpoint of the volume

For example:

...

tgz backup to your local machine and from there upload it to S3 or any other cloud storage solution you use.

Code Block
### On your local computer $ scp <your username>@<your server host>:<path to mssql-backup.tgz> ./

For example, in my case:

Code Block
$ scp centos@1.2.3.4:/home/centos/mssql-backup.tgz ./

This will copy the archive to my local computer from which point I can upload it to S3.

2. Restore

Pre-requisites

Considering that you have the mssql-backup.tgz file on your server under the centos user home folder you can follow these steps to restore the backup

1. Stop the containers

First and foremost you’ll need to stop the app and the database:

Code Block
$ docker compose down

You should see a message similar to

Code Block
[+] Running 3/3 ⠿ Container lakehouse Removed 1.1s ⠿ Container bplm Removed 0.4s ⠿ Network centos_bplm_net Removed

This tells you that the Lakehouse monitor and the DB are down.

2. Locate the MSSQL data

Now you will need to identify the path on the host system where your MSSQL data resides. Do this by using the following approach:

Code Block

$ sudo -i
# docker volume ls
DRIVER    VOLUME NAME
local     centos_mssql_data
local     centos_spring_session
# docker volume inspect centos_mssql_data
[
    {
        "CreatedAt": "2023-03-15T07:27:39Z",
        "Driver": "local",
        "Labels": {
            "com.docker.compose.project": "centos",
            "com.docker.compose.version": "2.16.0",
            "com.docker.compose.volume": "mssql_data"
        },
        "Mountpoint": "/var/lib/docker/volumes/centos_mssql_data/_data",
        "Name": "centos_mssql_data",
        "Options": null,
        "Scope": "local"
    }
]

In above example the backup_data would be in the


# cd /var/lib/docker/volumes/centos_mssql_data/_data
# ls -la
total 8
drwxrwx---. 7 root  root   73 Apr  5 12:36 .
drwx-----x. 3 root  root   19 Mar 15 07:27 ..
drwxr-xr-x. 5 10001 root   74 Mar 15 07:27 .system
drwxr-xr-x. 2 10001 root   24 Apr  5 12:36 backup
drwxr-xr-x. 2 10001 root 4096 Mar 15 07:27 data
drwxr-xr-x. 2 10001 root 4096 Apr  6 10:10 log
drwxr-xr-x. 2 10001 root   25 Mar 15 07:27 secrets

3. Clean the `_data` dir

You can either remove all of the data or, for extra-precations move it into another directory of your choosing.

3.1 Deleting the data

Code Block
# rm -rfv * .system

3.2 Moving the data

Code Block

# mkdir bak
# mv * .system bak
mv: cannot move 'bak' to a subdirectory of itself, 'bak/bak'
# ls -la
total 0
drwxrwx---. 3 root root 17 Apr  6 10:19 .
drwx-----x. 3 root root 19 Mar 15 07:27 ..
drwxr-xr-x. 7 root root 73 Apr  6 10:19 bak

4. Restoring the data

With the destination directory empty, it is now time to bring in the backup data.

Code Block

# cd ~centos
# tar zxvf mssql-backup.tgz -C /var/lib/docker/volumes/centos_mssql_data/_data/
./
./.system/
./.system/system/
./.system/system/Temp/
./.system/system/lsasetup.log
./.system/system/debug/
./.system/system/debug/PASSWD.LOG
./.system/system/system32/
......

As you can see I used the tar command to extract the contents of mssql-backup.tgz into the _data

folder on the host, specifically

directory (using the -C flag).

Now you can check that the data was correctly restored using the following approach:

Code Block
# cd - /var/lib/docker/volumes/centos_mssql_data/_data

...

4. Copy the file over to your local machine and on to S3

At this point, you can use scp to copy the file to your local machine

Code Block
scp <user>@<server>:master.bak ~/master.bak

And now simply upload the file to S3.

Note: on windows you will need to use a utility program such as WinSCP.

5. Restoring using the backup

...


# ls -la
total 8
drwxrwx---. 8 root  root   84 Apr  5 12:36 .
drwx-----x. 3 root  root   19 Mar 15 07:27 ..
drwxr-xr-x. 5 10001 root   74 Mar 15 07:27 .system
drwxr-xr-x. 2 10001 root   24 Apr  5 12:36 backup
drwxr-xr-x. 7 root  root   73 Apr  6 10:19 bak
drwxr-xr-x. 2 10001 root 4096 Mar 15 07:27 data
drwxr-xr-x. 2 10001 root 4096 Apr  6 10:10 log
drwxr-xr-x. 2 10001 root   25 Mar 15 07:27 secrets

5. Starting the containers

You can now exit the privileged bash session and start the containers.

Code Block
# exit $ docker compose up -d [+] Running 3/3 ⠿ Network centos_bplm_net Created 0.1s ⠿ Container lakehouse Started 0.8s ⠿ Container bplm Started

Give it 2 minutes during which time you can check to see that no restarts happen using docker ps

After 2 minutes without restarts you can check the app logs to see that the app started

Code Block
$ docker logs bplm \| grep Started ..... 2023-04-06 10:21:08.024 INFO 1 --- [ main] scala.App : Started App in 34.412 seconds (JVM running for 36.438)

6. Clean up of the `bak` folder

If in step 3 you chose to create the bak folder, it is now safe to delete it.

Code Block

$ sudo -i
# cd /var/lib/docker/volumes/centos_mssql_data/_data/
# ls -la
total 8
drwxrwx---. 8 root  root   84 Apr  5 12:36 .
drwx-----x. 3 root  root   19 Mar 15 07:27 ..
drwxr-xr-x. 5 10001 root   74 Mar 15 07:27 .system
drwxr-xr-x. 2 10001 root   24 Apr  5 12:36 backup
drwxr-xr-x. 7 root  root   73 Apr  6 10:19 bak
drwxr-xr-x. 2 10001 root 4096 Mar 15 07:27 data
drwxr-xr-x. 2 10001 root 4096 Apr  6 10:10 log
drwxr-xr-x. 2 10001 root   25 Mar 15 07:27 secrets
# rm -rfv bak

Version	Old Version 1	New Version 2
Changes made by	Denis Pitul	Denis Pitul
Saved on	Apr 05, 2023	Apr 06, 2023

Versions Compared

Key

1. Installing SQLCMD on CentOS 7

2. Backing Up the Master Database

3. Extracting the backup file from the docker container

Approach 1

1. Backing up

1. Stop the containers

2. Locate the MSSQL data

3. Archive the data

4. Start the containers

5. Use `scp` to download the data

Approach 2

2. Restore

Pre-requisites

1. Stop the containers

2. Locate the MSSQL data

3. Clean the `_data` dir

3.1 Deleting the data

3.2 Moving the data

4. Restoring the data

4. Copy the file over to your local machine and on to S3

5. Restoring using the backup

5. Starting the containers

6. Clean up of the `bak` folder

Page Comparison

Versions Compared

Key

1. Installing SQLCMD on CentOS 7

2. Backing Up the Master Database

3. Extracting the backup file from the docker container

Approach 1

1. Backing up

1. Stop the containers

2. Locate the MSSQL data

3. Archive the data

4. Start the containers

5. Use scp to download the data

Approach 2

2. Restore

Pre-requisites

1. Stop the containers

2. Locate the MSSQL data

3. Clean the _data dir

3.1 Deleting the data

3.2 Moving the data

4. Restoring the data

4. Copy the file over to your local machine and on to S3

5. Restoring using the backup

5. Starting the containers

6. Clean up of the bak folder

5. Use `scp` to download the data

3. Clean the `_data` dir

6. Clean up of the `bak` folder