I have script that monitors MS SQL backups on a Windows server and thought I would share it with everyone. Here it is in it’s entirety, and I’ll explain some of the details below.

#!/bin/sh
IFS=$’,’
###############################################
# SQL Server Backup Script written by Crimm #
###############################################

# Clear our log file
echo ” ” > /YOURLOGLOCATION/sqllog.log

# Change to our backup location
cd /YOURMOUNTPOINTHERE

# Set our variable
DATE=$(date +”%Y%m%d”)
YESTERDAY=$(date –date=@$(( $(date –date=$TODAY +%s) – 86400 )) +’%Y%m%d’)

# Error Checking
echo $DATE
#echo $YESTERDAY

# Check and see if the file exist. If not – ALERT – ALERT!
#!/bin/bash
FILE=”/YOURMOUNTHERE/YOURSCHEDULEDTASKHERE_$YESTERDAY*.txt”

# Error Checking
# echo $FILE
# head $FILE

if [ -f $FILE ];
then

# Unsetting IFS
unset IFS

echo ————————————— > /YOURLOGLOCATION/sqllog.log
echo [SQL Backup Script Email] >> /YOURLOGLOCATION/sqllog.log
echo [Script written by Crimm] >> /YOURLOGLOCATION/sqllog.log
echo [Instructions below] >> /YOURLOGLOCATION/sqllog.log
echo [Date of check $DATE] >> /YOURLOGLOCATION/sqllog.log
echo ————————————— >> /YOURLOGLOCATION/sqllog.log
echo -e >> /YOURLOGLOCATION/sqllog.log
echo ———————————————– >> /YOURLOGLOCATION/sqllog.log
echo [LIST OF .BAK and .TRN FILES] >> /YOURLOGLOCATION/sqllog.log
echo ———————————————– >> /YOURLOGLOCATION/sqllog.log
# List out the files
ls -n YOURBACKUPFILEHERE.bak >> /YOURLOGLOCATION/sqllog.log
echo -e >> /YOURLOGLOCATION/sqllog.log
ls -n YOURTRANSACTIONLOGBACKUPFILE.trn >> /YOURLOGLOCATION/sqllog.log
echo -e >> /YOURLOGLOCATION/sqllog.log
echo ————————– >> /YOURLOGLOCATION/sqllog.log
echo [Now the log files] >> /YOURLOGLOCATION/sqllog.log
echo ————————– >> /YOURLOGLOCATION/sqllog.log
echo [Now the log files] >> /YOURLOGLOCATION/sqllog.log
echo ————————– >> /YOURLOGLOCATION/sqllog.log
echo -e >> /YOURLOGLOCATION/sqllog.log
echo —————— >> /YOURLOGLOCATION/sqllog.log
echo [BACKUP 1] >> /YOURLOGLOCATION/sqllog.log
echo —————— >> /YOURLOGLOCATION/sqllog.log
head YOURSCHEDULEDTASKHERE_$YESTERDAY* >> /YOURLOGLOCATION/sqllog.log
echo -e >> /YOURLOGLOCATION/sqllog.log
echo -e >> /YOURLOGLOCATION/sqllog.log

# Instructions
echo ——————- >> /YOURLOGLOCATION/sqllog.log
echo [Instructions] >> /YOURLOGLOCATION/sqllog.log
echo ——————- >> /YOURLOGLOCATION/sqllog.log

echo Look at the dates on the backup files. >> /YOURLOGLOCATION/sqllog.log
echo If there was not one last night, then look at the log. >> /YOURLOGLOCATION/sqllog.log
echo Contact your Sys Admin for any failures >> /YOURLOGLOCATION/sqllog.log

# Send an email to tell us about it!
echo “Subject: ****** File Check: $DATE” | cat – /YOURLOGLOCATION/sqllog.log | /usr/lib/sendmail -f YOURFROMEMAIL.com -t YOURTOEMAIL.com
else
echo “Subject: ****** ALERT! ALERT! File Check: $DATE” | cat – /YOURLOGLOCATION/sqllogfail.log | /usr/lib/sendmail -f YOURFROMEMAIL.com -t YOURTOEMAIL.com
fi

What does it do?

1. Connects to a Windows mount
2. Sets a Date to yesterday’s date
3. Checks and sees if a log file for the backup exists. If not, alert alert
4. If exists then continue on
5. Write all details to log file
6. Email it out.

Things you’ll need to change?

• YOURLOGLOCATION – Change this to a place where the script can write a log
• YOURMOUNTPOINTHERE – Change this to the place where you want it to monitor for the file
• YOURSCHEDULEDTASKHERE – This is the name of your scheduled task. Remember to watch for spaces!!
• YOURTRANSACTIONLOGBACKUPFILE – This is the file name for your transaction log that you setup
• YOURBACKUPFILEHERE – This is the name of your backup file
• YOURFROMEMAIL.com – The email address you want the script to be from
• YOURTOEMAIL.com – The email address you want it to send the email to
• YOURLOGLOCATION/sqllogfail.log – This is just a message you create when the error is received. Just put whatever you want in here.

Questions? Please feel free to ask below.

What else can this do? With a little tweaking you can change it to monitor any type of file.