#!/bin/sh

# Script to compress and prune old SQL Server backups
# 2004/08/25  (c) Russell Howe

# 2004/10/12 - Updated to compress transaction log files (*.TRN)

# Where the backups live (D:\SQLBACKUP)
BACKUPDIR=/cygdrive/j/sqlbackup

# How many days of backups to keep (this is real days, not weekdays!)
# FIXME: Change to "How many copies of each database to keep"

KEEPDAYS=7


###################################################################

# PATH not set?!
PATH=/cygdrive/c/cygwin/bin

# Time in seconds since start of 1970 (GNU extension to date(1))
NOW="$(date +%s)"

# Convert KEEPDAYS to seconds
KEEPSECS="$((KEEPDAYS * 24 * 60 * 60))"

# Anything before this time gets torched.
DEATHPOINT="$((NOW - KEEPSECS))"

# To the backups!
cd "$BACKUPDIR"

# Only delete backups which have been compressed.
# This way, if the script goes nuts, it won't delete things until at least one
# day later (day 1, this script runs, compresses .bak file. Day 2, sees a .gz
# file, which it 'processes').

# If there are no .gz files, then "for file in *.gz" behaves oddly. Check explicitly.

ls *.gz > /dev/null 2>&1

if [ $? == 0 ]; then
	for file in *.gz; do
		FILEMTIME="$(stat -c %Y "$file")"
		if [ "$FILEMTIME" -lt "$DEATHPOINT" ]; then
			echo "Deleting $file! It is $(( ( DEATHPOINT - FILEMTIME ) / 3600)) hours too old!"
			rm -vf "$file"
		else
			echo "$file is less than $KEEPDAYS days old. Keeping."
		fi
	done
else
	echo No compressed backups found!
fi

# Database backups are .BAK files, transaction log backups are .TRN files.

# You might think this is possible in one ls command, but it isn't. ls *.bak *.trn
# only returns a success code iff there are both *.bak files AND *.trn files, so we
# have to do the listing twice and compare both response codes.

ls *.[Bb][Aa][Kk] > /dev/null 2>&1

bakret=$?

ls *.[Tt][Rr][Nn] >/dev/null 2>&1

trnret=$?

if [ "$bakret" == 0 -o "$trnret" == 0 ]; then
	echo "Compressing new backups"
	# If we don't nice this, the server grinds to a halt running gzip
	# Crappy process/IO scheduler

	# Silly case-insensitive Windows
	nice gzip -v9 *.[Bb][Aa][Kk] *.[Tt][Rr][Nn]
else
	echo "No backups to compress!"
fi