Shell script to backup Microsoft SQL Server vNEXT databases on Linux.

mssqlbackup

#!/usr/bin/env bash

#
# Shell script to automate the backup of Microsoft SQL Server vNEXT backups on Linux.
# Written by Bobby Allen <[email protected]>, 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 {} \;

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.

Hello,
Great script! Saved me a lot of time. May I ask you to write a script for restore multiply databases ? :)
I can't find anything on the web how to restore multiply MS SQL databases from a disk to MS SQL server on linux

Thanks you saved our time

i create a new file under /etc/cron.d with the name 'mssql-backups' and added this line for automation but it did not work
* * * * * root /usr/bin/mssqlbackup "var/opt/mssql/data/" 30 "localhost" "sa" "password123". Script work well and create db back up if i execute this file from terminal. Please help me sort out this issue

I presume that the issue is that you've not chmod'd the file correctly. Check that you have the right file permissions on the the file that you created under /etc/cron.d Hope this helps, Regards, Bobby

…

On Mon, 26 Apr 2021 at 12:29, Bilal Malik ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ i create a new file under /etc/cron.d with the name 'mssql-backups' and added this line for automation but it did not work * * * * * root /usr/bin/mssqlbackup "var/opt/mssql/data/" 30 "localhost" "sa" "password123". Script work well and create db back up if i execute this file from terminal. Please help me sort out this issue — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <https://gist.github.com/5d5f42db8ac78fc488ea25a0e5d4a051#gistcomment-3720085>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAF3NDAUEB6ROVFXTDJCMJLTKVFAPANCNFSM4J5ZTDAQ> .

thanks @allebb it works, i save a lot of time by using your magical script

That's great to hear - I'm pleased that I was able to help! All the best with your MSSQL backup script on Linux :)

…

On Mon, 26 Apr 2021 at 13:27, Bilal Malik ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ thanks @allebb <https://github.com/allebb> it works, i save a lot of time by using your magical script — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <https://gist.github.com/5d5f42db8ac78fc488ea25a0e5d4a051#gistcomment-3720154>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAF3NDAHWU3I2VYY5EII3D3TKVL43ANCNFSM4J5ZTDAQ> .