Skip to content

Instantly share code, notes, and snippets.

@allebb allebb/mssqlbackup
Last active Nov 11, 2019

Embed
What would you like to do?
Shell script to backup Microsoft SQL Server vNEXT databases on Linux.
#!/usr/bin/env bash
#
# Shell script to automate the backup of Microsoft SQL Server vNEXT backups on Linux.
# Written by Bobby Allen <ballen@bobbyallen.me>, 26/04/2017
#
# Download this script and put into your servers' /usr/bin directory (or symlink) then make it executable (chmod +x)
#
# Usage example (backup databases into /var/backups, keep backups for 5 days, connect to server "localhost" with the account "sa" and a password of "P455w0RD"):
# mssqlbackup "/var/dbbackups" 5 "localhost" "sa" "P455w0rD"
#
# If you wanted to automate backups, here is a cron job example (create a new file under /etc/cron.d eg. '/etc/cron.d/mssql-backups' and copy into the following line) -This will backup all your databases daily at 00:15 to /var/backups and keep backups for 30 days:
# 15 0 * * * root /usr/bin/mssqlbackup "/var/dbbackups" 30 "localhost" "sa" "<YourPasswordHere>"
#
# Set some default values
MSSQL_HOST="localhost"
BACKUP_DIR="/var/dbbackups"
BACKUP_DAYS=7 # You can test by changing file dates using: touch -d "8 days ago" mssql_20160504-1542.gz
MSSQL_USER="sa"
MSSQL_PASS=""
MSSQL_EXEC="/opt/mssql-tools/bin/sqlcmd"
DATE=`date +%Y%m%d-%H%M%S` # YYYYMMDD-HHMMSS
# 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 " MSSQL Password: **HIDDEN**"
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
# Disable error messages.
exec 2> /dev/null
# 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}/${DBNAME}_${DATE}.bak' WITH NOFORMAT, NOINIT, NAME = '${DBNAME}-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10"
echo "Done!"
done
echo "Backups complete!"
echo ""
# Re-enable error messages.
exec 2> /dev/tty
# Now delete files older than X days
find $BACKUP_DIR/* -mtime +$BACKUP_DAYS -exec rm {} \;
@bineagu

This comment has been minimized.

Copy link

bineagu commented Mar 5, 2018

Hello,

A suggestion from me is to replace
| grep -Ev "(-|Name|master|tempdb|model|msdb|affected\)$|\s\n|^$)"
with
| grep -Ev "(----|Name|master|tempdb|model|msdb|affected\)$|\s\n|^$)"

This is because any database that contains '-' will be omitted. It's a bit hackish but at least it works.

Anyway, thanks for sharing this.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.