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 withsqlcmd -?
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
Add Comment