Docker----基于docker搭建mssql(SQL Server)

一.搭建mssql数据库

1.使用docker搭建mssql

docker run -d --name mssql -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=ydt123456_' -p 1433:1433 -v /docker/mssql:/var/opt/mssql --restart always mcr.microsoft.com/mssql/server:2017-latest

• 这个密码需要复杂密码，要有大小写和特殊符号，替换成你自己的密码就行。如果是Linux服务器，可以不用端口映射，直接使用宿主模式。

docker run -d --name mssql --net host -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=ydt123456_'  -v /docker/mssql:/var/opt/mssql --restart always mcr.microsoft.com/mssql/server:2017-latest

2.登入容器

docker exec -it mssql bash

二.mssql连接与使用

1.连接到sqlcmd

/opt/mssql-tools/bin/sqlcmd -S 127.0.0.1 -U SA -P ydt123456_

2.执行SQL语句创建数据库

> create database testDB
> go

• 如果只想创建数据库，已经可以完成了，用Navicat Premium就可以连接到数据库了。

三.mssql备份与还原

1.运行sqlcmd备份数据库

docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P ydt123456_  -Q "BACKUP DATABASE test  to disk='/var/opt/mssql/backup/test.bak'"

2.还原数据库

docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P ydt123456_ -Q 'RESTORE DATABASE test FROM DISK = "/var/opt/mssql/backup/test.bak" WITH MOVE "test" TO "/var/opt/mssql/data/test.mdf", MOVE "test_Log" TO "/var/opt/mssql/data/test.ldf"

四.mssql数据库备份脚本

#!/bin/bash

ip=localhost
user=sa
passwd=ydt123456_

back_path=/docker/mssql/backup/
docker_path=/var/opt/mssql/backup/
db_name=test
back_time=`date +%Y%m%d_%H%M%S`
docker_filename=$docker_path$db_name$back_time
back_filename=$back_path$db_name$back_time
del_time=`date -d "7 day ago" +"%Y%m%d"`
del_backfile=$back_path$db_name$del_time

if [ ! -d "$back_path" ]; then
    mkdir -p "$back_path"
else
    cd "$back_path"
fi

docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -S $ip -U $user -P $passwd -Q "BACKUP DATABASE $db_name to disk='$docker_filename.bak'"

cd $back_path
tar -zcPf $back_filename.tar.gz $back_filename.bak
rm -f $back_filename.bak
if [ -e $back_filename.tar.gz ];then
    rm  -rf $del_backfile*.gz
    echo "database backup success!"
else
    echo "database backup failed!"
fi

执行脚本之后出现如下提示，代表备份成功

ydt@dt:/docker/mssql$ sudo bash mssqlback.sh
Processed 344 pages for database 'test', file 'test' on file 1.
Processed 2 pages for database 'test', file 'test_log' on file 1.
BACKUP DATABASE successfully processed 346 pages in 0.052 seconds (51.983 MB/sec).
database backup success!