How to Automate MSSQL Database Backups in Forge DO Server

The below tutorial does following steps

1. Create a database backup
2. Zip the backup
3. Receive email notification concerning backup results
4. Create a backup schedule
5. Delete old backups

1. Install PostFix MailUtils

To set up this solution, we need to install postfix mailutils. In Ubuntu we can do this as follows:


sudo apt-get update 
sudo apt-get install postfix mailutils

For the PostFix configuration. I selected the second option with Internet connectivity and SMPT configuration. Post that I selected the default options for rest of options provided.

2. Create Bash Script

Create a new directory named scripts on the the forge user's root directory i.e. /home/forge , create a new file named mssql_backup.sh


makdir scripts
cd scripts
touch mssql_backup.sh

Open the mssql_backup.sh file in the editor to paste the following backup script


# Backup storage directory
backupfolder=/tmp
# Notification email address
recipient_email=tushar@5balloons.info
# MSSQL user
user=sa
# MySQL password
password='useA$strongPas1337'
# Number of days to store the backup
keep_day=30
sqlfile=$backupfolder/backup-database-$(date +%d-%m-%Y_%H-%M-%S).bak
zipfile=$backupfolder/backup-database-$(date +%d-%m-%Y_%H-%M-%S).zip
# Create a backup
sqlcmd -S 127.0.0.1 -U sa -P $password -Q "Backup Database DATABASE_NAME To DISK='"$sqlfile"'"
if [ $? -eq 0 ]; then
  echo 'Sql dump created'
else
  echo 'backup command returned non zero code' | mailx -s 'No backup was created!' $recipient_email
  exit
fi
#Change Ownership
sudo chown forge $sqlfile
# Compress backup
zip $zipfile $sqlfile
if [ $? -eq 0 ]; then
  echo 'The backup was successfully compressed'
else
  echo 'Error compressing backup' | mailx -s 'Backup was not created!' $recipient_email
  exit
fi
rm $sqlfile
echo $zipfile | mailx -s 'Backup was successfully created' $recipient_email
# Delete old backups
sudo find $backupfolder -mtime +$keep_day -delete

Run the following command to make sure sql command line tools are available to root user , since we will be executing the file in sudo mode


sudo ln -s /opt/mssql-tools/bin/* /usr/local/bin/

Change file execution permission

Run the following command to make the file executable


chmod +x mssql_backup.sh

3. Configure cron Job

Cron allows you to schedule this script to run regularly. In order to facilitate this, do as follows:


sudo crontab -e

PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin:/opt/mssql-tools/bin
30 22 * * * /home/forge/scripts/mssql_backup.sh > /var/log/mssql_backup.log 2>&1

Then, add the script path to the end of the string. The logs of last executed script will be available at /var/log/mssql_backup.log

Thereafter, your script will be executed every day at 10:30 PM.

Comments