k8s 上 sts sqlserver 备份

sqlserver 备份

# mssql 全备
0 2   * * 1 /bin/sh /data/shells/mssql_backup.sh &> /dev/null

cat mssql_backup.sh

#!/bin/bash
# 设置mssql备份目录
folder=/var/opt/mssql/data/databack
day=`date +%F_%H-%M-%S`

# 数据库服务器，一般为localhost
host=localhost

# 用户名
user=sa

# 密码
password='Hdcnidu71bx.om'

# 容器名称
containerId='mssql-0'

# 循环获取数据库名称
for line in $(kubectl exec $containerId -- /opt/mssql-tools/bin/sqlcmd \
   -S $host -U $user -P $password \
   -Q "select [name] from master.dbo.SysDatabases where [dbid] > 4")
do
    # 判断是否是数据库名称
    if [[ $line =~ ^[a-zA-Z0-9_]+$ && $line != 'name' && $line != 'rows' ]]; then
        echo "数据库 $line 开始备份"
        kubectl exec $containerId -- /opt/mssql-tools/bin/sqlcmd \
        -S $host -U $user -P $password \
        -Q "BACKUP DATABASE [${line}] TO DISK = N'${folder}/${line}/${line}_${day}.bak' WITH NOFORMAT, NOINIT, NAME = N'${line} Backup ${day}', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
    fi
done

# 拷贝备份
mkdir /mssql_backup/${day} -p
cd /mssql_backup/${day}
kubectl cp ${containerId}:${folder}/ .

# 压缩
cd /mssql_backup/
tar zcf ${day}.tgz ${day}
rm -rf ${day}

# 删除容器内多余的文件
kubectl exec ${containerId} -- rm -rf ${folder}/

# 备份保留30天
find /mssql_backup/ -maxdepth 1 -type f  -mtime +30  -exec rm -rf {} \;