Showdown: MySQL vs. SQL Server 2005 (Backups Edition)

My disaster recovery needs from a database standpoint:

  1. Ability to restore to a point in time
  2. Retention period of 4 weeks
  3. E-mail notification on failure

MySQL Backups

A basic cron job dumping your databases on a daily basis:

shell> crontab -e

Add this to your crontab:


0 2 * * * mysqldump -ubackup_user -ppasswordhere --all-databases | gzip -c > /path/to/mysql_backups/backup_`date +%Y-%m-%d`.sql.gz

This will run at 2:00 AM daily and back up all of your databases. It will produce a fancy .sql.gz file that has a date stamp. Example: backup_2008-03-12.sql.gz. For generating Cron jobs, look at this site.

This is nice, but you’ll have to pay attention to your backup folder because eventually you will run out of disk space. You will not receive any notification when the backups fail or when you run out of disk space from this method. You also see that it gives anyone who has access to your crontab access to your password. Make sure this is a database user that has READ ONLY access to all dbs/tables.

SQL Server 2005 Backups

Some will say I’m a little biased… but this is one of the parts of SQL Server that makes it worth the price. If an image is blurry in the display, click it for full resolution.

  1. Create a new Maintenance plan
  2. Click ‘Back Up Database’ on the toolbox.
    toolbox.png
  3. Drag to the blank window.
  4. Select your databases, and various options for your backups. I recommend using ‘verify backup integrity’ and having your .bak files moved off site.
    ss-maintplanoptions.png
  5. Schedule your maintenance plan.
    schedule.png
  6. Add a notify operator task (you must have Database Mail Configured) , and drag the red node to it. This will email your specified operator (I get them sent to my phone) when a backup fails.
  7. Clean up! I do separate a weekly data cleanup maintenance plan to check my indexes, delete my backups older than a month, and lots of other things. Here’s a screenshot:
    ss-maintplan-extended.png

Clearly, SQL Server 2005 meets and exceeds my disaster recovery needs over MySQL. Did I mention it was extremely easy to set up in SQL Server Management Studio?

I didn’t have to research bash datestamps or find a site to generate crontabs.

Tim suggests I use this perl script for deleting old MySQL backups (run at the same interval as the backups):


#!/usr/bin/perl
use File::Find;
my $path = shift;
my $threshold = 60*60*24*28;
find(sub { /mysqlbackup\.tar\.gz$/i and
unlink "$_" and
print "$File::Find::name deleted\n\n" and
(( ((stat($_))[9] ) + $threshold) > time);
}, $path);

Also from the comments:

find /path/to/mysql_backups/ -type f -mtime +N -exec rm -rf {} \;

USE AT YOUR OWN RISK.

Advertisements