#!/bin/bash
  
#保存备份个数，备份31天数据
day=31
#备份保存路径
backup_dir=/backup
#日期
dd=`date +%Y-%m-%d-%H-%M-%S`
#用户名
username=sa
#密码
password=123456
#备份数据库
db_arr="db1 db2 db3"

#如果文件夹不存在则创建
if [ ! -d $backup_dir ];
then
    mkdir -p $backup_dir;
fi

for dbname in ${db_arr}
do
    db_dir=${backup_dir}/${dbname}
    #对每个数据库建立单独的文件夹
    if [ ! -d ${db_dir} ];
    then
        mkdir -p ${db_dir};
    fi
    #备份
    bak_file=${db_dir}/${dbname}-${dd}.bak
    /opt/mssql-tools/bin/sqlcmd -S localhost -U "${username}" -P "${password}" -b -Q "BACKUP DATABASE [${dbname}] TO DISK = N'${bak_file}' WITH NOFORMAT, NOINIT, NAME = '${dbname}-full', SKIP, NOUNLOAD, STATS = 10"
    cat ${bak_file} | gzip > ${bak_file}.gz
    rm ${bak_file}
    echo "create ${bak_file}.gz" >> ${backup_dir}/log.txt
    #删除过期备份
    delfile=`ls -l -crt  ${db_dir}/*.bak.gz | awk '{ print $9 }' | head -1`
    count=`ls -l -crt  ${db_dir}/*.bak.gz | awk '{ print $9 }' | wc -l`
    if [[ ${count} -gt ${day} ]]
    then
      #删除最早生成的备份，只保留day数量的备份
      rm ${delfile}
      #写删除文件日志
      echo "delete ${delfile}" >> ${backup_dir}/log.txt
    fi
done