SQLServerの基本操作(Docker環境, インストール, データ操作,バックアップ,リストア)

説明
開発環境
SQLServerをDockerを使ってインストール
ローカル接続
データベース作成
GUIツールで接続
テーブル作成＆データ操作
バックアップ＆リストア

説明

SQLServer操作の初心者の方の手助けと思い、ちょっとした基本操作を以下に記載していきます。

開発環境

・windows10 home
・VirtualBox
・Vagrant
・Docker version 19.03.14, build 5eb3275d40
・docker-compose version 1.27.4, build 40524192

SQLServerをDockerを使ってインストール

docker-compose.ymlファイルを用意します。(今回ボリューム設定はしていません。)

version: '3'
services:
    mssql:
      image: mcr.microsoft.com/mssql/server:2019-latest
      container_name: mssqlserver
      ports:
        - 1433:1433
      environment:
        - ACCEPT_EULA=Y
        - SA_PASSWORD=あれこれ
        - MSSQL_PID=Express
        - MSSQL_LCID=1041
        - MSSQL_COLLATION=Japanese_CI_AS

エラー対応１

メモリが2Mバイト以上ないとコンテナは作成できません。DockerToolBoxを使っている方は、VirtualBoxのメモリを1024MBから2048MBにしましょう。

SQL Server 2019 will run as non-root by default.
This container is running as user mssql.
To learn more visit https://go.microsoft.com/fwlink/?linkid=2099216.
sqlservr: This program requires a machine with at least 2000 megabytes of memory.
/opt/mssql/bin/sqlservr: This program requires a machine with at least 2000 megabytes of memory.

エラー対応2

SQLServerのパスワード設定は、8文字以上で、大文字、小文字、10進数、記号の4つのセットのうちの3文字になります。

ローカル接続

sqlserverの初期ユーザは「SA」になります。以下のコマンドでローカル接続を行います。

$ docker exec -it mssqlserver /bin/bash
mssql@2f53795661aa:/$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P あれこれ

パスが通ってないので、bin以下まで指定しないといけないのはめんどいですね。

データベース作成

1> create database testdb
2> go

1> use testdb
2> go
データベース コンテキストが 'testdb' に変更されました。

1> SELECT name, database_id, create_date FROM sys.databases;
2> go

・
・
testdb

GUIツールで接続

今回は、VisualStudioCodeで接続します。
1. 拡張機能機能から、SQL Server（mssql）をインストールします。

2. ctrl + shift + P で、「MS SQL：Connect」を選択すると、SQLServerのパッケージがインストールされます。

3. 左メニューにSQLServerのアイコンをクリックし、「Add Connection」をクリック
・Server name or ADO connection string → 192.168.99.100
・Database name → testdb
・Authentication Type → SQL Login
・User name → SA
・Password → あれこれ
・Save Password →パスワードを保存するかどうか
・Profile Name → 特にないので、何も入力せずエンター

・ ctrl + shift + P で、「MS SQL：New Querry」で、sqlが作成できます
※（sql文を作成時にエラーが起きた場合、VisualStudioCodeを再起動しましょう！)

テーブル作成＆データ操作

テーブル作成

1> Customer (id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, name VARCHAR(100) NOT NULL, age INTEGER, address VARCHAR(100));
2> go

テーブル確認

1> SELECT * FROM sys.objects WHERE type = 'U' ORDER BY name;
2> go

データの挿入(※以下のageの[]がうまく表示できませんでした。)

"1> INSERT INTO Customer ([Name], 'age', [address]) VALUES ('鈴木', 20, '東京');"
"2> INSERT INTO Customer ([Name], 'age', [address]) VALUES ('佐藤', 21, '大阪');"
"3> go"

データの確認

1> select * from Customer;
2> go

テーブルを空にする

1> truncate table Customer;
2> go

バックアップ＆リストア

準備

コンテナにルートユーザで入って

$ docker exec -u 0 -it mssqlserver /bin/bash
root@2f53795661aa:/# apt-get update
root@2f53795661aa:/# apt-get install vim less

バックアップ

バッチファイル(Linux)の作成（backup.sh）

root@2f53795661aa:/# vi backup.sh
#!/bin/sh

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P あれこれ -i /var/backups/backup.sql > /var/backups/backup.log

バッチファイルの権限変更

root@2f53795661aa:/# chmod +x backup.sh

バックアップのsqlファイルの作成

root@2f53795661aa:/# vi /var/backups/backup.sql
backup database testdb to disk='/var/backups/testdb.bak' with init
go

バックアップフォルダの権限を変更する

root@2f53795661aa:/# chmod 777 -R /var/backups

バックアップの実行

root@2f53795661aa:/# sh backup.sh
root@2f53795661aa:/# ls -la /var/backups/
total 3404
drwxrwxrwx 1 root  root    4096 Oct 17 08:01 .
drwxr-xr-x 1 root  root    4096 Aug 27 07:27 ..
-rwxrwxrwx 1 root  root     336 Oct 17 08:01 backup.log
-rwxrwxrwx 1 root  root      70 Oct 17 08:01 backup.sql
-rw-r----- 1 mssql root 3461120 Oct 17 08:01 testdb.bak

testdb.bakが作られているのが確認できました。

※dockerコマンドと組み合わせて使う場合

$ docker exec -u 0 mssqlserver /opt/mssql-tools/bin/sqlcmd -S 192.168.99.100 -U SA -P あれこれ -i /var/backups/backup.sql

リストア

上記のバックアップの続きで、testdb.bakをtest2dbに移します。
まずはtest2dbを作成します。

root@2f53795661aa:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P あれこれ
1> create database test2db
2> go

※同じSQLServer上に、testdbが残っていると「testdb使用中です。」のようなエラーが出て、リストアできないので、testdbを削除するか、別のSQLServerで実行する必要があります。
※排他アクセス可能にする方法はあるっぽいですが、今回は省きます。

リストアコマンドを実行します。

root@2f53795661aa:/# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P あれこれ -Q "RESTORE DATABASE test2db FROM DISK = '/var/backups/testdb.bak' WITH REPLACE" > /var/backups/restore.log
root@2f53795661aa:/#
root@2f53795661aa:/# cat /var/backups/restore.log
データベース 'test2db' の 408 ページ、ファイル 1 のファイル 'testdb' を処理しました。
データベース 'test2db' の 2 ページ、ファイル 1 のファイル 'testdb_log' を処理しました。
RESTORE DATABASE により 410 ページが 0.056 秒間で正常に処理されました (57.128 MB/秒)。