Shell script to backup Microsoft SQL Server databases on Linux.

mssqlbackup

#!/bin/bash
MSSQL_HOST="localhost"
BACKUP_DIR="/data/mssql/backup/"
BACKUP_DAYS=4
MSSQL_USER="sa"
MSSQL_PASS=''
MSSQL_EXEC="/opt/mssql-tools/bin/sqlcmd"
DATE="$(date +%Y%m%d)"

  # Set Backup Parameters
  if [[ ! $1 == "" ]]
  then
    BACKUP_DIR=$1
  fi
  if [[ ! $2 == "" ]]
  then
    BACKUP_DAYS=$2
  fi
  if [[ ! $3 == "" ]]
  then
    MSSQL_HOST=$3
  fi
  if [[ ! $4 == "" ]]
  then
    MSSQL_USER=$4
  fi
  if [[ ! $5 == "" ]]
  then
    MSSQL_PASS=$5
  fi

  echo ""
  echo "Starting MSSQL backup with the following conditions:"
  echo ""
  echo "  Backup to: ${BACKUP_DIR}"
  echo "  Backup timestamp: ${DATE}"
  echo "  Keep backups for ${BACKUP_DAYS} days"
  echo ""
  echo "  MSSQL Server: ${MSSQL_HOST}"
  echo "  MSSQL User: ${MSSQL_USER}"
  echo ""

  # If the backup directory does not exist, we will create it..
  if [[ ! -d $BACKUP_DIR ]]
  then
    echo ""
    echo "Backup directory does not exist, creating it..."
    mkdir -p $BACKUP_DIR
    echo ""
  fi

  # Connect to MSSQL and get all databases to backup...
  DATABASES=`$MSSQL_EXEC -S "$MSSQL_HOST" -U "$MSSQL_USER" -P "$MSSQL_PASS" -Q "SELECT Name from sys.Databases" | grep -Ev "(----|Name|master|tempdb|model|msdb|affected\)$|\s\n|^$)"`

  # Iterate over all of our databases and back them up one by one...
  echo "Starting backups..."
  for DBNAME in $DATABASES; do
    echo -n " - Backing up database \"${DBNAME}\"... "
    $MSSQL_EXEC -H "$MSSQL_HOST" -U "$MSSQL_USER" -P "$MSSQL_PASS" -Q "BACKUP DATABASE [${DBNAME}] TO  DISK = '${BACKUP_DIR}/${DATE}_${DBNAME}.BAK' WITH NOFORMAT, NOINIT, NAME = '${DBNAME}-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10 --with compression"
    find "${BACKUP_DIR}" -type f -name '*.BAK' -exec tar czvf {}.tgz {} \; -exec df -h {} \; -exec rm -v {} \;
    echo "Done!"
  done
  echo "Backups complete!"
  echo ""
exit