[WIP] Local SQL server backup and restore procedures

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:

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:

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:

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:

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:

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

For example:

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.

# docker ps
CONTAINER ID   IMAGE                                               COMMAND                  CREATED        STATUS        PORTS                                                                                        NAMES
ec90f0e0c174   blueprint.azurecr.io/bpcs/lakehouse-optimizer:2.3   "./start-app.sh"         28 hours ago   Up 28 hours   2222/tcp, 0.0.0.0:4040->4040/tcp, :::4040->4040/tcp, 0.0.0.0:80->8080/tcp, :::80->8080/tcp   bplm
2b07a8da8b82   mcr.microsoft.com/mssql/server:2019-latest          "/opt/mssql/bin/perm…"   3 weeks ago    Up 47 hours   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).

Now, use docker cp to copy the backup file to the host system.

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:

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

For example:

# 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

/var/lib/docker/volumes/centos_mssql_data/_data

folder on the host, specifically

/var/lib/docker/volumes/centos_mssql_data/_data/backup/master.bak

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

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

TBD