How to Automate MSSQL Database Backups in Forge DO Server

The below tutorial does following steps

  1. 1. Create a database backup
  2. 2. Zip the backup
  3. 3. Receive email notification concerning backup results
  4. 4. Create a backup schedule
  5. 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

makdir scripts
cd scripts

Open the file in the editor to paste the following backup script

# Backup storage directory
# Notification email address
# MSSQL user
# MySQL password
# Number of days to store the backup
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 -U sa -P $password -Q "Backup Database DATABASE_NAME To DISK='"$sqlfile"'"
if [ $? -eq 0 ]; then
  echo 'Sql dump created'
  echo 'backup command returned non zero code' | mailx -s 'No backup was created!' $recipient_email
#Change Ownership
sudo chown forge $sqlfile
# Compress backup
zip $zipfile $sqlfile
if [ $? -eq 0 ]; then
  echo 'The backup was successfully compressed'
  echo 'Error compressing backup' | mailx -s 'Backup was not created!' $recipient_email
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

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
30 22 * * * /home/forge/scripts/ > /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.

Site Footer