Monday, June 14, 2010

Powershell Backup All Databases Locally and to Amazon S3

Previously I had posted a Powershell script that would use 7Zip to compress and encrypt a folder and then send it to Amazon S3. This script could have many uses, but in my case it was designed to fulfill the need for off site storage of compressed database backups. While it did all the heavy lifting of getting the data encrypted, compressed and uploaded to S3 I left the creation of the database backup folder as an exercise for the user to complete.

I now have a working system that I would like to share in case anyone was unable to create the backup folder using Powershell. There were a few hiccups that I found, such as not being able to use the Start-Transcript method in a SQL Agent Job and having my MSDB data file triple in size after 3 months of backups, but after working out those issues the script provides a very cost effective method of creating both local and offsite database backups with 1 week or 1 month retention. Currently I have it running on a server with about 100 databases ranging in size from 7MB to 700MB with daily full backups and hourly differential backups being sent to Amazon S3 and stored with 1 month retention for about $5 a month. The total raw backup size is 2.2GB per full backup set and 100-400MB for each differential backup set, but the script gets about 10:1 compression so the daily storage on S3 is about 500MB total for all of the full and differential backups.

You will need to modify the settings in both the psBackupAllDBtoAmazonS3.ps1 and ps7ZiptoAmazonS3.ps1 files to get this to work on your server, but once you do you can sit back and let the scripts do it’s magic. You can set it to run as a scheduled task, but we chose to run it as an SQL Agent Job so that our server monitoring software would notify us if there were any issues with the script.

Blog.TheG2.Net - Your guide to life in the Internet age.