VPS やクラウドの Linux サーバーで、SQL Server を Docker のコンテナとして動かす例が増えています。
手軽に始められる反面、「バックアップをどうするか」は自分で考える必要があります。
コンテナの中やサーバーの中だけにバックアップがあると、サーバーが壊れたときに一緒に失われてしまいます。
この記事では、BACKUP DATABASE でバックアップを作り、scp で社内の Windows パソコンへダウンロードして、それを毎日自動で行うまでの手順を、コマンドの例つきでご紹介します。
目次
1. 全体の流れ
- SSH で Linux サーバーにログインする
- コンテナの中の
sqlcmdでBACKUP DATABASEを実行し、.bak ファイルを作る - .bak ファイルを、ホスト(Linux サーバー)から読める場所に置く
scpで社内のパソコンへダウンロードする- サーバーに残った .bak ファイルを消す
Windows 10・11 には ssh と scp が最初から入っているので、ほかのソフトを入れなくても作れます。
2. 準備するもの
- SSH でログインできるユーザーと鍵:この記事では
ubuntuユーザーで、サーバーの IP を203.0.113.20、コンテナの名前をmssqlとしています。 - docker コマンドを使える権限:
ubuntuユーザーをdockerグループに入れておくか、sudoで実行します。 - バックアップ用の SQL Server のログイン:
saを使わずに、バックアップするデータベースでdb_backupoperatorの役割を持つログインを作っておくと安全です。
3. バックアップ用のフォルダを用意する(最初の 1 回だけ)
SQL Server のコンテナは、データを /var/opt/mssql に置きます。
ここをホストのフォルダにつないで(ボリュームのマウント)おくと、コンテナの中で作った .bak ファイルを、ホストからそのまま読めます。
services:
mssql:
image: mcr.microsoft.com/mssql/server:2022-latest
container_name: mssql
volumes:
- ./mssql-data:/var/opt/mssql
- ./mssql-backup:/var/opt/mssql/backup
SQL Server 2019 以降のコンテナは、mssql というユーザー(UID 10001)で動きます。
バックアップ用のフォルダは、このユーザーが書き込めて、SSH のユーザーも読めるようにしておきます。
mkdir -p ~/mssql-backup
sudo chown 10001:$(id -g) ~/mssql-backup
sudo chmod 775 ~/mssql-backup
ポイント:ボリュームをつないでいない場合は、docker cp mssql:/var/opt/mssql/backup/xxx.bak ~/ でコンテナの外へ写すこともできます。
ただし、コンテナの中とホストの両方に一時的に同じ大きさのファイルができるので、ディスクの空きに注意してください。
4. BACKUP DATABASE で書き出す
コンテナの中の sqlcmd を使って、バックアップを作ります。
データベース名を SalesDB とした例です。
docker exec mssql /opt/mssql-tools18/bin/sqlcmd -S localhost -U backup_user -P 'パスワード' -C \
-Q "BACKUP DATABASE [SalesDB] TO DISK = N'/var/opt/mssql/backup/SalesDB_20261122.bak' WITH COPY_ONLY, INIT, CHECKSUM"
- COPY_ONLY:ふだんの差分バックアップやログのバックアップの流れに影響しない、「単独のコピー」としてバックアップを取ります。
ほかのバックアップの仕組みと一緒に使うときは、必ず付けておきます。 - CHECKSUM:書き出すときにデータの壊れがないかを確かめます。
- -C:sqlcmd(mssql-tools18)で、サーバーの証明書を信頼するための指定です。
古いイメージでは、sqlcmd が/opt/mssql-tools/bin/sqlcmdにあり、-Cはいりません。
イメージによっては sqlcmd が入っていないこともあるので、最初に確かめてください。
BACKUP DATABASE は、データベースを使いながら実行できます。
ポイント:WITH COMPRESSION(バックアップの圧縮)は、SQL Server の Express エディションでは使えません。
Express のときは、書き出したあとに gzip で圧縮すると、ダウンロードの時間を短くできます。
5. 社内へダウンロードする
# 社内のパソコンで(PowerShell)
scp -i C:\backup\vps.key ubuntu@203.0.113.20:mssql-backup/SalesDB_20261122.bak D:\backup\sqlserver\
ダウンロードできたら、サーバーのファイルは消しておきます。
ssh -i C:\backup\vps.key ubuntu@203.0.113.20 "rm -f ~/mssql-backup/SalesDB_20261122.bak"
フォルダの持ち主が mssql(10001)で、.bak ファイルも mssql のものになるため、ubuntu ユーザーで消せないときは sudo rm にします。
6. タスク スケジューラで毎日自動にする
パスワードは、サーバーのファイル(chmod 600)に置き、スクリプトに直接書かないようにします。
#!/bin/bash
# 使い方:bash ~/run_backup.sh データベース名 ファイル名(拡張子なし)
set -e
DB=$1
F=$2
PW=$(cat ~/.mssql_backup_pw)
docker exec mssql /opt/mssql-tools18/bin/sqlcmd -S localhost -U backup_user -P "$PW" -C -b \
-Q "BACKUP DATABASE [$DB] TO DISK = N'/var/opt/mssql/backup/$F.bak' WITH COPY_ONLY, INIT, CHECKSUM"
sudo chmod 644 ~/mssql-backup/$F.bak
-b を付けると、SQL のエラーのときに sqlcmd がエラーの終了コードを返すので、失敗に気づけます。
$key = "C:\backup\vps.key"
$srv = "ubuntu@203.0.113.20"
$dest = "D:\backup\sqlserver"
$db = "SalesDB"
$name = $db + "_" + (Get-Date -Format "yyyyMMdd_HHmm")
ssh -i $key $srv "bash ~/run_backup.sh $db $name"
if ($LASTEXITCODE -ne 0) { throw "BACKUP DATABASE に失敗しました" }
scp -i $key "${srv}:mssql-backup/$name.bak" $dest
if ($LASTEXITCODE -ne 0) { throw "ダウンロードに失敗しました" }
ssh -i $key $srv "sudo rm -f ~/mssql-backup/$name.bak"
# 30 日より古いバックアップを消す
Get-ChildItem $dest -File | Where-Object LastWriteTime -lt (Get-Date).AddDays(-30) | Remove-Item
最後に、Windows のタスク スケジューラで、このスクリプトを毎日決まった時刻に動かすように登録します。
- 操作:
powershell.exe、引数:-NoProfile -ExecutionPolicy Bypass -File C:\backup\sqlserver_backup.ps1 - 「ユーザーがログオンしているかどうかにかかわらず実行する」を選ぶ
- 初めての接続のときに出る確認で止まらないよう、一度手で
sshを実行してyesと答えておく
スクリプトの中で sudo を使うときは、パスワードを聞かれずに実行できるように、サーバーの sudoers で必要なコマンド(chmod・rm)だけを許可しておきます。
7. 自分で作るときに気をつけたいこと
| 気をつけたいこと | なぜ必要か |
|---|---|
| 失敗したときの通知 | スクリプトが止まっても、誰も気づかないことがいちばん危険です。 メールなどで知らせる仕組みが必要です。 |
| ディスクの空き | .bak ファイルはデータベースとほぼ同じ大きさになります。 サーバー側に消し忘れがあると、ディスクがいっぱいになり、データベースまで止まることがあります。 |
| 別の場所への複写 | パソコンが壊れたときのために、NAS や外付けの HDD にも写しておくと安心です。 |
| Windows Server の SQL Server | 相手が Windows Server のときは scp ではなく、共有フォルダ(SMB)から写すなど、作り方が変わります。 |
| 担当者が変わったとき | スクリプトの中身がわかる人がいないと、トラブルのときに直せなくなります。 |
8. 元に戻すとき(RESTORE DATABASE)
バックアップが正しく読めるかは、RESTORE VERIFYONLY で確かめられます。
RESTORE VERIFYONLY FROM DISK = N'/var/opt/mssql/backup/SalesDB_20261122.bak' WITH CHECKSUM;
別の名前のデータベースとして戻して、中身を確かめることもできます。
ファイルの名前(論理名)は RESTORE FILELISTONLY で確認できます。
RESTORE DATABASE [SalesDB_CHECK]
FROM DISK = N'/var/opt/mssql/backup/SalesDB_20261122.bak'
WITH MOVE N'SalesDB' TO N'/var/opt/mssql/data/SalesDB_CHECK.mdf',
MOVE N'SalesDB_log' TO N'/var/opt/mssql/data/SalesDB_CHECK_log.ldf';
いざというときに戻せるかを、ときどき試しておくことをおすすめします。
この手順を、画面で設定するだけに
弊社の ITW Backup は、この記事の手順(BACKUP DATABASE → 社内へダウンロード → サーバーのファイルを削除)を、画面で接続先・曜日と時刻・保存場所を入れるだけで自動で行うツールです。
- Linux・Docker・Windows Server の SQL Server に対応
- 2GB 未満のデータベースは、SQL Server への接続だけでバックアップ(SSH や共有フォルダの準備はいりません)
- バックアップは COPY_ONLY で取るので、今のバックアップの流れに影響しません
- 成功・失敗をメールでお知らせ(失敗したときだけにもできます)
- NAS や外付け HDD への 2 段階保存、保存する期間の設定
さくらの VPS(Docker)、さくらのクラウド(Windows Server)での導入実績があります。
30 日間、製品版と同じ機能を無料でお試しいただけます。